MGMT2006 - Creating relationships between two tables
1. BEFORE you create any queries or reports, you should create a relationship among your tables. To do so:
Access 2007: select Relationship from the Database Tools menu.
Access 2003: select Relationship from the Tool menu. You should see a window SIMILAR to the one
IF YOU SEE a BLANK Screen, then right click and select Show Table.
2. Select the first table, click ADD, and also ADD the other tables. Then click Close. If this window does not
appear, right click in the gray area and select the SHOW WINDOW option.
3. Now, we need to link the tables together. Look at the fields in each table. You should note that there is a
common field between two tables. There may be more, but look at two tables at a time and for that common
field. This is the field we will use. Select this field in one table and while holding down you left mouse
button, DRAG it to the next field in the next table, don’t worry if you see a ‘no entry’ sign appear as you
drag! Once you are reached the next field, release the mouse button and another window should appear:
IF YOU See the Word INDETERMINATE in the relationship type area at the bottom
of the window, then LOOK for the SAME field in another table to join. See example
4. In this window, you need to click next to ‘enforce referential integrity’. Then click Create. This window
below illustrates one-to-many.
a. Look where the table name is (supplier on the left at the top) and where the ‘one’ is (on the left
at the bottom)
b. Look where the table name is (Product on the right at the top) and where the ‘many is (on the
right at the bottom)
c. It tells you that there is One Supplier ‘supplying Many Products!
5. Notice that in the diagram below, a line is drawn from Supplier (with a 1 next to it) to Product (with an
infinity sign next to it), even though we may have dragged from Product to Supplier. Access determined the
type of relationship...