We said in the section on tables that it would be useful if we could lookup some information from a table rather than entering it directly into the wizard. Using a table as the lookup source makes it easier to manage the data through datasheet view or a form and it allows us to add extra information about the subject.
Follow the approach outlined earlier to create a second table for details of the recording label.
| Field Name | Data Type | Field Properties |
| MusicType | Text | Size=20 |
| Description | Memo |
Set the primary key to MusicType by clicking in the MusicType row and then click on the yellow key in the toolbar.
Enter some data as follows:
We can now change the design of the CDs table to take account of the new table. Open the CDs table in Design view and select the Category (music type) field. Select Lookup wizard from the data type list and this time choose the first of the two options from the first stage of the wizard
In stage two of the wizard choose the table or query from which you will choose the lookup data;
In stage three of the wizard choose the field or fields you want to have included in the lookup; in this case it is just one field, the LabelName:
The next stage asks you to sort the chosen fields into order - choose the most appropriate for your data, typically ascending.
The next stage of the wizard lets you fine-tune the display of the lookup data:
The final stage asks you to give the lookup a name:
When you click Finish a message will appear asking you to save the changes to the table:
Now when you view the Category field the user will see the lookup items you specified:
In Design view, under the Lookup tab for the Category field, the following instruction in Structured Query Language (SQL, more on this later) appears in place of the list we saw earlier:
As well as information about music genres we can have additional tables to provide details on other fields in the CDs table, such as the label, the tracks and details of the artist. It is a feature of relational databases like Access that they follow the principles of relational database design established at IBM in the 1980s. There is more information on this topic in the notes on databases, which you could either read now or continue with the practical work and pick up the theory later.
Follow the approach outlined earlier to create a new table for details of the recording label.
| Field Name | Data Type | Field Properties |
| LabelName | Text | Size=30 |
| Address1 | Text | Size=30 |
| Address2 | Text | Size=30 |
| Address3 | Text | Size=30 |
| Address4 | Text | Size=30 |
| ManagingDirector | Text | Size=30 |
Establishing a primary key for this table is a little taxing as there may be more than one label with the same name, not in the same country perhaps, but this could still be a problem. We could use an 'autonumber' to stamp each label with a unique number but this is more suited to objects like invoices which need a number; it is much easier to refer to a record label by its name rather than by some 8-digit code. We can make do with LabelName for now but a stronger alternative is LabelName_Address1 as it is very unlikely that two labels would have the same name and the same address. It is better to sort out such problems in advance by analysing the real-world situation rather than guessing and having to make costly corrections later.
One important point about the primary key in this table is that it happens to be the same as one of the non-primary keys in the CDs table (Label). We will use this dual occurrence to establish a relationship between the tables so that we can find more detailed information about the record label.
Now enter some data for this table - find the details on the internet or on your CDs.
You can now repeat the change to the lookup criteria for the Label field by setting the lookup wizard to point to the LabelName field in the Labels table.
Go through the CDs table and make sure that every Label entry and every Category entry is set to something in the new lookup list; this will be important later.