Microsoft Office 2010: Creating Table Relationships
For the purpose of this exercise, we will create a "movie inventory" database
We want to create relationships between tables in a single database so that we can simply choose categories from a drop-down menu for certain fields such as: type of media, genre, and location. To do this, we can create an additional table in our database (which we'll call Table 2).We will link data from Table 1 with data from Table 2 to create a relationship between these tables.
1. Navigate to the “Create” tab on your Microsoft Access ribbon and select “Table”
2. A new blank table will appear in your open database, which, by default, is called “Table 2”
3. Table 2 will include, in this exercise, the fields needed to populate the “genre” or “category” drop-down in our movie inventory table
We are going to connect Table 1 and Table 2 data within the “Category” field:
1. Toggle to Table 1 and in Design View within the “Category” field, use the drop-down under Data Type to select the “Lookup Wizard”
2. A series of dialog boxes will walk you through the steps to connect the tables'
Lookup Wizard Step 1
Lookup Wizard Step 2
Lookup Wizard Step 3
Now that you've selected Table 2 to connect to Table 1, the fields to be connected should be chosen. We are Connecting the category field in Table 1 with the Category field in Table 2.
Lookup Wizard Step 4
We've connected the Category fields in Tables 1 and 2. Below, the Lookup Wizard offers a sample of the drop-down options within the Category field in your media inventory database. It is recommended that you hide your "Key Column" or column that designates each record as separate within your database.
Now we were able, as you can see above, to create a relationship between two tables. You can create another relationship between Table 1 and Table 3. To do so, create table 3 using the same steps you used to create Table 1 and create options for "Media Type:" book, CD, DVD, Blu-Ray, etc.
Sample Record from Table 1 should appear in Table View as: