Access 2007: Table Settings
As you've learned, when you create a new database Access starts you off with a single table that displays in Datasheet view for entering data. Design view provides a more powerful interface for formatting your table. You can use Design view to add, rearrange, and move fields, but you cannot use it to add data. Some settings you can adjust in Design view include: Data Type, Primary Key, Blank Fields, Default Values, Duplicate Values, Input Masks, Validation Rules, and Lookups.
To switch between Datasheet and Design view, use any of the following methods. Note all of the tasks covered below require you to have your table open in Design view. Also be aware when you switch back to Datasheet view after making changes in Design view you will be prompted to save your table.
- Right-click the current table's tab and select the desired view from the pop-up menu that appears.
- Click either
[Design
view button] or 
[Datasheet
view button] in the lower-right corner of the Access window. - Select the Home tab in
the Views group on the Ribbon and click or
(depending
on which view you currently have displayed).
- Select the Home tab in the Views group
on the Ribbon and click
[down
arrow button]. Select the desired
view from the pop-up menu.
In addition to the three basic data types (text, numbers, and dates), Access provides a number of more specialized data types. There also are related field properties you can adjust based on the selected data type to further define how a field should be formatted. For example, if you have a text data field, you could set the maximum length, or for a numeric field, the number of decimal places it should allow. To change a field's data type and properties:
- Locate the field to update and then select the corresponding cell under the Data Type column.
- Click
[Down
arrow button] and select the new data type from the pop-up menu that appears. - Change properties by right-clicking the field name and selecting "Properties" from the pop-up menu. The Field Properties are displayed on the General tab at the bottom of the window so that you can make changes. For example, to reduce the default Field Size, select the existing value and type a new one.
Every table must have a primary key. The primary key is a field (or combination of fields) that organizes your data by uniquely identifying each record. By default, tables are sorted by primary key. When you create a table in Datasheet view, Access automatically creates a primary key field and labels it ID. You can change this name to something more descriptive if you like (e.g., Employee ID, Customer Number, etc.). If your table does not have a primary key field, perhaps because you created it in Design view, it is up to you to specify one. To do this:
- Select the field to use as the primary key. If necessary, add a new field
by right-clicking the
[Row
Selector] column cell just below where you want to add a row. Select "Insert
Rows" from the pop-up menu that appears and then type a Field
Name and select a Data Type. - Assign a field as the primary key by selecting the Design tab
in the Ribbon and clicking
[Primary
Key button] in the Tools group.
Alternatively, you can right-click the Row Selector column
and select
"Primary Key". - Remove an existing primary key by first deleting any table relationships
involving the key (see the Table Relationships)
lesson. Right-click the
[Primary
Key icon] to the left of the field name and deselect "Primary Key" in
the pop-up menu that appears.
Primary keys must be unique for each record; leaving a primary field blank is not an option. If your table does not contain something like a phone number or employee number, it is best to use an AutoNumber field so Access will sequentially assign numbers to each record in the table.
By default, every field in a new table (other than the primary key) is optional. To assure a particular field cannot be left blank, you can make it required. To do this:
- Select the field to modify by clicking its name in the Field Name column at the top of the window.
- Click the Required line on the General tab under Field Properties at the bottom of the window.
- Change the default value by clicking [Down
arrow button] and selecting "Yes".
- Ensure a single space entered in a field cannot result in an essentially blank field by setting Allow Zero Length to "No".
An alternative way to handle a field is by defining a default value. In this case, Access automatically inserts predefined text or a number. To define a default value:
- Select the field to hold the default value by clicking its name in the Field Name column at the top of the window.
- Click the Default Value line on the General tab under Field Properties at the bottom of the window.
- Type the desired number or text; if you input text, it must be enclosed in quotes (for example, "OSU").
- Create more dynamic default values by clicking
[More
button]. Here you can build your own custom expression.
As you have learned, every table should contain a primary key to make each record unique. However, in some cases you also may want to prevent duplicate information from being entered in another field (for example, two employees sharing the same payroll record number). You can force a field to require unique values by adding an index. This helps Access determine if you would be duplicating an existing field value when adding a new record. To do this:
- Select the field to index by clicking its name in the Field Name column at the top of the window.
- Click the Indexed line on the General tab under Field Properties at the bottom of the window.
- Change the default value by clicking [Down
arrow button] and selecting "Yes (No Duplicates)".
Input masks provide a way to control how data is entered into a field; for example, to automatically format telephone numbers or dates using a particular format. You can use input masks for Text, Numbers, Currency, and Date/Time data types. To add an input mask, do the following:
- Select the field to work with by clicking its name in the Field Name column at the top of the window.
- Click the Input Mask line on the General tab under Field Properties at the bottom of the window.
- Access the Input Mask Wizard by clicking [Wizard
button]. Here you can select from a number of preformatted masks.
The Wizard works only with Text or Date field types. If the
field you selected is a different type, you must manually enter the
value in the Input Mask field on the General tab.
If you do not see the exact format you want when using the Wizard, you can
edit formatting by clicking 
;
selecting the mask to update; and making the changes. You also can add a
new format to the Wizard by clicking 
[New
(blank) record].
While input masks can be used to control how information is entered according to a single unchanging pattern, Validation Rules can be used to apply more sophisticated restrictions to data entered in a field (for example, verifying that a number falls within a specified range, or that a date hasn't yet occurred, or even that text starts with a certain letter). The rule you create tells Access which values to allow and which ones are no good. To do this:
- Select the field to work with by clicking its name in the Field Name column at the top of the window.
- Click the Validation Rule line on the General tab under Field Properties at the bottom of the window.
- Type a validation expression and press [Enter]; Access will then reformat
the expression appropriately. Alternatively, you can use the Expression
Builder to create a validation rule by clicking [More
button].
- With the field still selected, add an error message that will appear when a value fails the check by clicking the Validation Text line and typing the desired text.
Creating a validation rule may seem simple, but to get the
result you want, you will need to know more forming expressions. For more
information, click 
[Online
Help button] and search for "Validation Rules."
Minor variations in a data field can cause problems when you try to analyze the data. A way around this is to use Lookups to provide a way of standardizing your data by allowing you to select from a ready-made list of choices. For example, you might put all courses offered in such a list. To create a Lookup with fixed values, do the following:
- Locate the field to work with in the Field Name column at the top of the window. Check to see that the field has the correct data type (text and number are the most common types used in conjunction with lookups).
- Access the Lookup Wizard by clicking the field's Data Type cell
and then the [Down
arrow button]. Select "Lookup Wizard" from the pop-up menu that appears.
- Select "I will type in the values that I want" in the dialog box and
click
. - Type the values for the lookup column in the space provided and click .
- Type a label for the lookup column and check the selection box if you want to allow multiple values in the field.
- Click
to close the Wizard.