Create and Modify Tables: Types, Properties, Validation, and Relationships
The Create and modify tables domain tests whether you can design a table that enforces data integrity: choosing the right data type, setting field properties, writing validation rules, and wiring relationships.
Data types and field properties
Short Text defaults to 255 characters; Long Text holds memo data; Number exposes Field Size, Format, and Decimal Places; Currency avoids rounding; AutoNumber, Yes/No, Date/Time, Attachment, and Calculated are also available. Key properties include Caption, Default Value, Required, Indexed, Input Mask, Format, and - for text fields only - Allow Zero Length. A Lookup field stores the underlying value type (Short Text or Number) plus a combo box display control; "Lookup" is not a storage type.
Validation
A field-level Validation Rule is checked whenever that field is saved; a table-level rule, entered in the table's Property Sheet in Design view, can compare several fields, for example [DueDate]>=[StartDate]. Validation Text replaces the default error message. Because the database engine enforces rules, they also apply to queries and forms.
Keys and relationships
A primary key may be a single field or a composite of up to ten fields (select them in Design view, then Primary Key). In the Relationships window, check Enforce Referential Integrity, then optionally Cascade Update Related Fields and Cascade Delete Related Records. A one-to-one relationship requires a unique index on the foreign key.
Worked example
A rule must reject order dates earlier than today, showing "Order date cannot be in the past." Reasoning: on the OrderDate field set Validation Rule to >=Date() and Validation Text to that message. Use Date() rather than Now(), because Now() includes a time component that makes today's date appear earlier than the current moment.
Traps
- Typing the message into the Validation Rule property.
- Applying Allow Zero Length to a Number field.
- Setting Required after nulls already exist - Access warns before enforcing it.
- Building a table-level rule that names a field with spaces but no brackets.