applied tips : april 2009
microsoft access : all about relationships
A relationship is what links two tables together. For instance, you may have an Organizations table and a Contacts table. Each contact is employed by a specific organization through a relationship. Likewise, “InvoiceLines” are part of an Order, and Orders are bought by Organizations. With multiple tables in a database, many relationships often exist.
In order for relationships between tables to work, those tables have to have a primary key. The primary key for each table is the unique identifier (often set as the AutoNumber data type) that distinguishes each record from all the other records.
As an example, no two employees would ever share the same EmployeeID. As employees are terminated, their EmployeeIDs are never reused (even if the employee is deleted from the table). When an invoice is prepared it can be linked to the employee who prepared it by storing the EmployeeID in the Invoices table.
To turn a field into a Primary Key for its table, select the field while in Design View and click the Primary key icon.
Note that if you attempt to save a new table without specifying a Primary Key, Access will ask you if it should create one for you. If you already have an AutoNumber field, it will use this as the Primary Key, otherwise it will create one for you.
Lookups (Drop-Down Menus)
One convenient way of creating a relationship between two tables is to set up a Lookup field which has the nice side-effect of making a drop-down menu. For instance, when creating a new record for the Invoices table you could select the CustomerID for the invoice by pulling down a list of customers rather than CustomerIDs. Although Access stores the CustomerID, the user sees the customers by name instead.
These fields usually store numeric data since they link back to an AutoNumber field in the related table.
To create a lookup field, first create the field as a regular numeric field. Typically, the name of such a field is “CustomerID” or “EmployeeID”. The caption, however, might just read “Customer”.
After setting its properties, pull down the data type menu again and select “Lookup Wizard”. This will launch the Lookup Wizard to help you create the drop-down menu and establish the relationship between your tables.
In the first step of the wizard, you can opt to have the drop-down menu show values from another table (choice #1) or from the values you want to enter (choice #2). Unless you never expect the data to change (such as Male/Female) you should always base your menu off another table. If that table doesn’t exist yet, create it before launching the wizard.
In the second step of the wizard, select the table or query that will drive your drop-down menu. Later you will be able to customize the sort and filter criteria.
In the third step of the wizard, you can select which field(s) should appear in the menu when pulled down. It is not necessary to select the Primary Key here. Be selective about your choice, as too much data will clutter the screen and make it difficult for your users.
The fourth step allows you to specify a sort order for your pull-down menu.
The fifth step allows you to modify the column widths and optionally hide the Primary Key column (recommended). After this, you can finish the wizard.
Refining a Lookup
The Lookup Wizard only goes so far in the creation of the lookup. You may wish to enhance the drop-down menu or fine-tune the formatting of the lookup in a variety of ways.
When a lookup is selected in Design View, the properties window will give you a number of properties you can modify for that field in a second tab called “Lookup”.
The Row Source is the SQL statement that drives the pull-down choices and can be modified by clicking the ellipsis icon. If you add or remove rows from the Row Source, be sure to adjust the other Lookup properties such as the number of columns and the column widths.
Defining and Modifying Relationships
The easiest way to define a relationship is to start by building a Lookup between your two tables (see below). Once this is done, the relationship is automatically created although you might want to modify its structure.
In Access 2002 or 2003, from the Tools menu choose Relationships. In Access 2007, show the Database Tools tab and click Relationships. The Relationships window will appear as a diagram of tables and links between them. Primary Keys will appear in bold lettering.
Building official relationships between tables will speed up the process of writing queries, forms, and reports for you later, since Access will assume these relationships as defaults.
To create a relationship, drag the Primary Key from one table onto the desired match of another table (called the Foreign Key).
To delete a relationship, select the line with your mouse and press Delete on your keyboard. Deleting a relationship does not affect your data or any existing Queries, Forms, or Reports.
To modify a relationship, double click the line with your mouse.
There are three kinds of relationships:
One-to-Many – This is the most common relationship type. You’re specifying that each record on the “left side” of the relationship can have many records on the right, but each record on the right can belong to only one record on the left. For example, one organization might have many orders, but an order only belongs to one organization. One-to-One – This relationship is not used very often. You use this to “extend” the data for a record into another table – often for data that not every record needs, or for security reasons when you want to segregate your information. For example, a small handful of your organizations might have extra fields that take up considerable space in the database. Rather than making these fields for each organization, they can be moved to a second table and linked with a one-to-one relationship back to the original organizations table. Many-to-Many – This type of relationship allows us to specify that multiple records from one table may be linked to multiple records of another table. For example, a table of Products can have a Many-to-Many relationship with a table of Suppliers. Any product can come from any number of suppliers, and any supplier can provide you with any number of products. In Access, you use a third “Junction” table to establish a list of these relationships.
Access automatically defines the relationship type for you based on the type of fields you are linking and their properties.
Enabling Referential Integrity on a relationship tells Access to limit your ability to change and delete records (for the protection of your data). For example, you would not want to delete a Customer if they had Invoices in the system, as this would affect your financial reports.
The following rules are enforced when Referential Integrity is enabled:
- The foreign key field must match a primary key or be null. For example, this will prevent you from linking an Invoice to an invalid Customer Number.
- Records that have other related records cannot be deleted. For example, you will not be able to delete a Customer if they have one or more Invoices.
- You will not be able to change the value of a primary key, if that record has related records. For example, you cannot change a Customer ID if that customer has invoices. Note that if you are using AutoNumber for the primary key, this is already in effect.
Enabling this feature forces Access to automatically delete child records when a parent record is removed from the database, overriding Referential Integrity Rule #2. For example, if you delete a Customer with Cascading Deletes enabled, Access will automatically delete all of that Customer’s child records (Contacts, Invoices, Payments, etc).
Access will display a warning before carrying out the delete procedure.
Enabling the Cascade Update feature disables Referential Integrity Rule #3, permitting you change the value of a Primary or Foreign Key, and automatically cascading that change through any related records. For example, if you were using UPC as the Primary Key for your products, changing a product’s UPC in the Product table would automatically change all related records in the Invoice Lines table, preserving referential integrity.
Note that if you are using AutoNumber as the Primary Key, you still won’t be able to change it.
When building a query using two related tables, there are three ways that records from these tables can be displayed:
- Inner Join – Results from both tables will be displayed wherever there was a match on the primary/foreign keys. Records that didn’t match are hidden. For example, in a query linking Books to Publishers, a record will be displayed for each match of book to publisher. Any books without a publisher, and any publishers without a book, will not be displayed.
- Left Outer Join – All records from the “left table” will be displayed, and any matching records from the “right table” will be appear. If the Books table were on the left, we would see all books (regardless if they had a publisher), and only the publisher fields when matched to a book. Publishers without a book will not appear.
- Right Outer Join – The same as a Left Outer Join, but with all records from the “right table” displayed, and only those from the “left table” when matched to a record in the “right table”.
When writing a query, it’s very important to consider the join type. A wrong setting will hide records that you intended to appear, or show records you didn’t intend to see.
schedule a class or get consulting on access>
Request an on-site Access class from Applied Office or get some helpful consulting. Learn more here