Microsoft Access 2003 - Intro

1. Microsoft Access 2003 - Intro Class 6 Relationships

2. Relationships Between Tables A marriage unites two families through one common bond: the married couple. Two tables can be related (or united) through one common field.

3. Types of Relationships • One-to-one • One entry in each table corresponds to only one entry in another table • Not common – used to • Avoid exceeding the 255 field number maximum per table • Control access to sensitive data (ie: hourly rate for an employee) • One-to-many • Many records in one table relate to a single record in the primary table • Most common • Many-to-many • Defined between two tables and a junction table which contains common fields for each table

4. Using the Relationship Window • In the main Database Window, click the Relationships button in the toolbar. • A Show Table screen will appear where you select the tables that you want to include in the relationship. Click the Add button or double-click the table names to add them to the window. • Once you have added the tables you want, close the Show Table screen. • To remove a table from Query Design View, simply click once on the desired table (box), and press the Delete key on the keyboard. • To add more tables to Query Design View, click the Show Table button on the toolbar

5. Creating a Relationship Between Two Tables • In the Relationship Window, simply drag the common field from one table to it’s match in the second table. • An Edit Relationship screen will appear. • Click the Create button to make a simple relationship between the tables. • (Choose to enforce Referential Integrity to make a complete, secure relationship between tables)

6. Referential Integrity • Requires that a foreign key value in a related table match the value of the primary key for some new row in the primary table • Prevents the occurrence of orphaned records • Controls the order of operations in the database • The following conditions must be met in order to enforce referential integrity (assuming no data in tables) • The field on the one side of the relationship (in the primary table) must be the primary key • The fields on which the tables are joined must be the same data type (exception – an Autonumber field can be related to a Number field • Both of the tables that are related must belong to the same database • Once referential integrity is set • Cannot enter a value in the foreign key field or a related table if the primary table does not contain a matching value • Cannot delete a record from a primary table if there are matching records in the related table

7. Cascade Update & Delete • Cascade Update • A change in the primary key of the primary table will automatically be updated in the related table • Cascade Delete • Deleting a record in the primary table automatically deletes any related records in the related table.

8. More Relationship Trivia • The line that joins the two tables in a relationship is called a JOIN, or “Relationship Line” • Primary keys are depicted as BOLD in the table boxes in the Relationship Window. • The matching common field to the primary key is called the Foreign Key • In a relationship between two tables: • The table that contains the primary key is called the Primary Table • The table that contains the foreign key is called the Secondary (or Related) Table

9. Multiple Table Query Customer table Orders table  1 Customer ID Customer Address Customer Phone Contact Name Customer ID Order Number Item Ordered Quantity Price Invoice Total Customer ID Customer Address Item Ordered Quantity Price Invoice Total New query using both tables

10. This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 License.