Advanced Module – Databases (Access)

Lesson 2 – Advanced Tables and Field Properties

This lesson covers advanced table design features in Access, including data types, field properties, input masks, default values, validation rules and lookup fields. These skills support accurate data entry and strong database structure.

1. Data types (advanced use)

Choosing the correct data type ensures accuracy, improves performance and prevents invalid values.

• Short Text – up to 255 characters (names, codes).
• Long Text – long descriptions or notes.
• Number – numeric values (requires correct Field Size).
• Currency – money values; prevents rounding errors.
• Date/Time or Date/Time Extended – precise date/time storage.
• Yes/No – True/False values.
• AutoNumber – automatically generated IDs, typically used for primary keys.
• Lookup Wizard – creates drop-down lists from another table or value list.

How to change a data type

1. Open the table in Design View.
2. Select the field.
3. Choose a data type from the Data Type drop-down list.
4. Save the table (Access may warn if existing data could be lost).

2. Field size and format

Field Size controls storage requirements and valid numeric ranges.

• Integer / Long Integer – whole numbers.
• Single / Double – decimal values.
• Smaller field sizes improve performance.

Format controls how values appear:

• Currency (£)
• Short Date (dd/mm/yyyy)
• Percentage (%)

How to set field size and format

1. Open the table in Design View.
2. Select a Number or Currency field.
3. Set Field Size (e.g. Long Integer).
4. Set Format (e.g. Currency, Short Date).

3. Default values

Default Value automatically fills a field when a new record is added.

• Default OrderDate to Date().
• Default Country to “United Kingdom”.

How to set a default value

1. Open the table in Design View.
2. Select the field.
3. In Field Properties, enter a value in Default Value.
4. Save the table.

4. Input masks

Input masks control the pattern of data entry, such as phone numbers or postcodes.

How to add an input mask

1. Open the table in Design View.
2. Select a text field (e.g. PhoneNumber).
3. In Field Properties, click the Input Mask field.
4. Click the … button to open the Input Mask Wizard.
5. Choose a built-in mask or create a custom one.
6. Finish the wizard and save.

5. Validation rules and messages

Validation rules prevent invalid data from being saved, improving data integrity.

• Field-level validation – applies to a single field (e.g. Quantity >= 0).
• Table-level validation – checks conditions between multiple fields.

Example field validation rule

For a DiscountPercent field:

• Validation Rule: Between 0 And 100
• Validation Text: “Discount must be between 0 and 100.”

6. Lookup fields (combo boxes in tables)

Lookup fields allow users to select a value rather than type it, reducing errors.

Creating a lookup field from another table

1. Open the table in Design View (e.g. Orders).
2. Select the field name (e.g. CustomerID).
3. Set Data Type to Lookup Wizard….
4. Select “I want the lookup field to get values from another table”.
5. Choose the related table (e.g. Customers).
6. Select key and descriptive fields (CustomerID, FullName).
7. Hide the key field if appropriate.
8. Finish the wizard and save.

7. Indexing fields

Indexes improve performance when searching or sorting.

• Index fields that are searched often (Surname, Postcode).
• Avoid indexing every field — this increases file size.
• Use No Duplicates indexing when unique values are required.

How to set an index

1. Open the table in Design View.
2. Select the field to index.
3. In Field Properties, change Indexed to:
   • Yes (Duplicates OK)
   • Yes (No Duplicates)
4. Save the table.

8. Practical Activity

• Open your database and choose one main table.
• Adjust at least three fields to better data types or field sizes.
• Add a Default Value and a Validation Rule to one numeric field.
• Add an Input Mask to a phone or postcode field.
• Create a Lookup field that pulls values from another table.
• Index a field that users will commonly search.