Access Basics:
Table
Relationships
|
Access Basics:
|
|
|
|
||
|
You finally have two tables that have a relationship. The relationship was created by the Lookup Wizard in the previous lesson. Access will recognize matching fields between tables in some cases. In other situations you will have to show Access what fields to match and what kind of relationship it is: One-to-Many, Many-to-Many, or One-to-One. Examples below are from starwars.mdb. Relationships WindowAccess provides a special window for viewing relationships. No tables are shown automatically. You must pick which ones you want to see.
Relationships window with its toolbar
Join lines connect the matching fields between tables. A line with a
1 at one end and the infinity symbol
Example of referential integrity: Access would not let you delete a supplier from the Suppliers table if that supplier's ID was included in Star Wars Collectibles table. |
|
Project 2: Access Basics
|
Relationships Toolbar
The Relationships window has its own toolbar, with three new buttons:
Start with: mytrips.mdb from previous lesson View RelationshipsThere is a special window for viewing Relationships. In the previous lesson, the Lookup Wizard said it needed to create a relationship. Let's look at that relationship.
Delete a RelationshipWhy would you want to delete a relationship? If you need to make changes to the table design, Access may not let you while there is a relationship to another table. Of course, you need to be careful that the relationship will still work after you make your changes!
Create a Relationship ManuallyTo create a relationship manually is a simple drag and drop action. You will create a new join between the two tables, but it will not be quite like the earlier one.
Print RelationshipsThe Print button on the Relationships toolbar is grayed out. Unexpectedly, the File menu does contain a command for printing this window, Print Relationships.
View/Edit SubdatasheetA table that is in a One-to-Many or One-to-One relationship has a new feature. Each record on the "One" side has a subdatasheet that shows the related records from the other table.
Test Referential IntegrityLet's see what Access will do now that it must enforce referential integrity on your new join.
|
||||||
|
~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~ |
at the
other indicates a One-to-Many relationship for which Access will enforce
Show Table = opens a dialog where you can pick which tables and queries
to show in the Relationships window.
Show Direct Relationships = Shows the relationships that have been
defined for the selected table.
Show All Relationships = Shows all of the relationships that have been
defined in the database.
From
the menu select 
or
When you delete a table from
the Relationships window, you do not delete the table itself. You do not
delete any relationships either. You have changed only what is shown in
the window.
Click
on
Close
the Relationships window.
In
the Relationships window, click on the line connecting the two
tables. The line gets thicker.
Press
the Delete key or click the Delete button on the toolbar.
Click
Yes.
The
Edit Relationships dialog appears, showing the table names and the field
names.
Click
the button Join Type...
In
the Edit Relationship dialog, click in the box Enforce Referential
Integrity. This choice tells Access not to allow changes that would break
the connection between these tables. 
Click
on the button Create. 
the
Save button on the toolbar to save this layout.
Click
the File menu to open it and select 
Press
the F11 key to switch to the Database Window. (A useful shortcut when
that window is covered up by others!)
Click
the expand button
at the left
end of the record for Jorge Martinez. 
In
the subdatasheet for Hector Chavez, change the 
collapse button at
the left of the record.
Select
the record for Hector Chavez by clicking the record selector at
the left end of the record.
Press
the Delete key to delete the record.
