How to Create a Table in Design View in Access 2013
Design view provides a different view of the same table that you see in Datasheet view. Design view provides more options for controlling data entry. This is good for maintaining data integrity.
You can also switch to Design view by clicking the Design icon at the bottom-right corner.
The Datasheet icon next to it allows you to toggle between Datasheet view and Design view as needed.
How Design View Displays the Fields
In Design view, the fields are listed in rows (as opposed to columns like in Datasheet view). The first column lists the field names, the second column contains their respective data types.
Design view doesn't display any data (like in Datasheet view).
When you select a field in Design view, the bottom pane displays its properties. The field properties specify extra information about the type of data that can be entered into that field by a user.
Field properties are optional — you don't need to specify anything other than what's already there. However, field properties are a powerful way to maintain data integrity. By adjusting one or more field properties, you can ensure that your database doesn't get populated with eroneous data.
The Field Properties pane (at the bottom of the screen) allows you to specify certain properties against each field. Here are some of the more commonly used ones.
The Format property allows you to specify the format that the data is displayed. Note that it doesn't affect how the data is stored — only the way it's displayed to the user.
Microsoft Access provides predefined formats for Date/Time, Number and Currency, Text and Memo, and Yes/No data types.
The Input Mask property helps you control how the data is entered into the field. It allows you to ensure that the field contains only data that is acceptable to that field.
For example, you might want to make sure that whenever a user enters a date into a date field, that it is always in a certain format. If you don't do this, your data could start to look messy. One user might enter the date as "20 Sep 2016", another enters it as "20/09/16", another enters it as "09/20/2016", and another accidentally enters a phone number, etc.
In this case, you could use an input mask that requires the date to always be entered in only one format. If the date is not entered in the correct format, the record won't be saved.
A Default Value is a value that is added to the field as soon as the record is created — without requiring the user to enter anything in that field.
Providing a default value can be useful in cases such as providing a timestamp (when the sytem automatically inserts the current time/date into the field) or providing initial user preferences (such as newsletter opt-in, etc).
A Validation Rule restricts what users can enter into a field. It is a rule that you set up in order to prevent incorrect data being entered.
For example, a validation rule of <500 will ensure that only numbers less than 500 are entered. A validation rule of >500 And <1000 ensures that the number is between 500 and 1000.
A validation rule can also take the value from another field. For example, you could use [EndDate]>=[StartDate] to ensure that the value of the EndDate field is no earlier than the value of the StartDate field for a given record.
You can specify a validation rule by either typing the rule directly into the Validation Rule field, or by using the Expression Builder.
The Validation Text property allows you to provide a message to users when they try to enter invalid data (i.e. data that doesn't pass the valiation rule).
The Required property allows you to specify whether the field is a required field or not (i.e. whether the user can leave the field blank or not).
Show Date Picker
You can use the Show Date Picker property to enable the user to select the date from a date picker. A date picker is a mini pop-up calendar that allows the user to browser to the date and select it by clicking on it.
Note that you can't use a date picker on fields that have an input mask. If you need to use a date picker, you'll need to remove any input masks against that field.
The Expression Builder
The Expression Builder can help you find a suitable expression for a field. It can help you do things like create a validation rule (to ensure data is entered correctly), or specify a default value for a field.
Using the Expression Builder is optional. If you know the expression you need, you can just type it without launching the Expression Builder. However, the expression builder can help you browse for a suitable function, constant, operator, or identifier to assist you in building an expression for the field.
To launch the expression builder, click thebutton at the right side of the property that you wish to apply an expression to.
The Input Mask Wizard
The Input Mask Wizard is a tool that can help you determine an input mask for the field. Similar to the Expression Builder above, the Input Mask Wizard allows you to browse through a list of options.
To launch the Input Mask Wizard, click thebutton at the right side of the Input Mask row. The Input Mask Wizard will launch a small popup on top of the current window.
There are many other data types that can benefit from input masks. For example you could use an input mask for phone numbers, social security numbers, addresses, and more.
Your input mask doesn't need to match the way the data is displayed. For example, you could use an input mask to ensure users enter dates as "27/09/1969" but have the dates displayed as "27 Sep 1969".