Microsoft Access Field Properties – Lookups

Lookups

Field properties are where you fine tune the table. The data type provides general

information about the type of data that a field is going to contain and in the properties is

where you define it more exactly

In the linked tables the data that is stored that makes the connections to other tables is the key field in the other table, which is normally an ID field in some way - a Customer ID or Product ID.  You cannot always deduce who or what the customer is from just the ID and so have to refer to the other table.  To make it easier a lookup can be created and it will automatically do this for you. 

The lookup field can be done in several ways, each of them give the same results. 

1. Select the field that needs the lookup

2. Change the data type to lookup Wizard

3. Select the first option where the lookup column looks up the data from another table

4. Then Choose the table that you want to lookup the data from

5. Select field(s) you wish to see by clicking on the single arrow button in the middle or by double       clicking on the appropriate field name(s)

6. Set the sort order

7. Adjust the column widths by going to the dividing line in the column headings (if necessary)

8. Add column heading (if you want it to be different to the field name)

9. Click on Finish – this will then prompt you to save the table as this saves the relationship that you have created between the two tables

The Lookup wizard can also be started in design view by using the Lookup Column button on the ribbon.  This will insert a new field at the cursor position. 

In the datasheet view (where you can add data), the lookup column can be set up by right clicking on the column head and selecting the Lookup Column. 

NB:  if you have already created a relationship on the field that you wish to set up as a lookup the original relationship has to be broken otherwise the Lookup Wizard will not work.