How-To: Build Relationships Between Tables Using Microsoft Access

Curriculum Area:  English/ Mathematics/ Studies of Society and Environment/ Science

Grade Level: Yrs 5 and up

Application: Microsoft® Access 2000

Tip: Build Relationships Between Tables Using Microsoft Access

Description: Students build a relationship between tables. Once relationships are made, students can view the data from the different tables at the same time, or even create queries and reports based on the new relationships. To set relationships between tables, students establish a link between key fields in each table that contain common information such as an identification (ID) number.

Did You Know?
Every table should contain a primary key, especially when you want to build a relationship between tables. A primary key is one field (sometimes more) whose contents are unique to each record. It's a way of identifying each record in a database. For example, the primary key in a student table could be the Student ID field because each student's record would contain a different ID number; no two records would have the same number.

If you had a database for a baseball card collection or a musical CD collection, each unique card or CD would have an ID number so you could identify each unique item in your list. In this example, the ID number would be the primary key in the table.

Classroom Applications:
  1. A table (list) of animals in a particular biome, country, or continent, or of a particular species. Another related animal table might list each animal's characteristics, such as what it eats, how fast it runs, and how long it lives. The tables can be related to each other through a Name field (as long as each animal's name is different). Each Name field in each table should be the primary key.
  2. A table of inventors including their name, their country of origin, and the name of their invention. Another related table keeps track of each invention and its uses, as well as how the invention benefits society. The tables can be related to each other through the inventor's name or an ID number for each inventor. Each Name field or ID Number field in each table should be the primary key.
  3. A table of Australian states and their capitals. A related table tracks each state's facts such as the state flower, flag, and so on. There can even be a natural resources table for each state. Of course, the State Name field would most likely be the primary key for these tables.

How To:
  1. First, in Microsoft Access create your tables. For instance, in the States database in example #3 above, a State Capitals and Nicknames table would be one table; another would be a State Statistics table; and another would be an Economy table.
  2. Be sure to set a primary key in the main table as well as in the additional table(s). To set a primary key, make sure you are in Design View, and then click within the field that you want to make the primary key. Click the Primary Key button (picture of a key) on the toolbar.
  3. Enter some data into your tables. Notice that the tables are independent of each other. In other words, while viewing one table, you can't view the related data from another table because there isn't a relationship established yet.

    For example, if you were viewing the State Capitals and Nicknames table, you might want to also view the data from the State Statistics table at the same time. Relationships let you do this!
  4. To create a relationship between tables, click the Relationships button on the toolbar.
  5. Click the Show Table button to view all of the tables in the database.
  6. Double-click the tables that you would like to add to your Relationships list (the ones you want to connect or link together). When you are finished, close the Show Table dialog box.
  7. Drag the primary field (for example, State Name) from the main table to the related field (for example, State Name) in the other table, and then release the mouse button. The Edit Relationships dialog box appears.
  8. Click Create to establish a relationship between the two tables. (Often this is a One-To-One type of relationship, in which one record matches a single record in the other table.)

    You will now see your two tables, with a line connecting them by their common fields.
  9. Close the Relationships dialog box, and click Save to save this new relationship.
  10. Now go back to one of your tables and double-click it to see the change in Table view. You will now see a plus sign next to each record. When you click a plus sign, another table appears on the same screen as the main table. You are now viewing the related data from the other table!
  11. Continue exploring the new view of your data.
Questions to Ponder:
  • Does this new view help you understand the data better?
  • Can you find new connections between the data?
  • Can you compare the facts better through this view?
For Advanced Users:
  • Try creating some queries that pull fields from the various linked tables.

    You will see that the data matches up -- that is, one record from one table corresponds with the related record/data in the other table (based on the related field). For example, when you are using the States database, information for each state from the State Capitals and Abbreviations table can be viewed, along with some fields from the State Statistics table. A list showing each state's name, capital, and population can be viewed, now that there are relationships between the tables.

© 2003 Microsoft Corporation. All rights reserved. Microsoft, Microsoft Press, FrontPage, Access, Outlook, PowerPoint, Visio and Windows Media, MapPoint, SharePoint are registered trademarks of Microsoft Corporation in the United States and/or other countries.