INLS261 Tools for Information Literacy

[starters] <previous session] [next session>

[RDBMS & tables] [relationships] [forms & simple queries] [more queries & reports] [lab]
[relationships] [subtask 5.2] [next time]

Relationships - the key to RDBMS

Primary and Foreign Keys

A relationship requires the primary key of one table be inserted as a foreign key in a second table. The link between the two like attributes is the relationship.

Access Relationships Help

tables created manually do not have set relationships
primary and foreign keys should share the same name and must share the same datatype
after you have established a relationship, then you will only need to enter data in one table for the related tables to be updated with the new data (will become more clear in queries which create dynamic tables of data)
to view/create relationships in select Database Tools > Relationships (or select the Relationships icon on the toolbar)

[top]

Types of relationships

one-to-one (unusual)

in a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A

one-to-many (most common)

in a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A
note that your table properties provide you advice about how to ensure the relationships are based on the proper field properties.

many-to-many (less common)

in a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A
in Access, this type of relationship is only possible by defining a third table (called a junction table)

[top]

Creating a relationship in Access

You have tables and you have the potential for a relationship, but until you actually link the tables together, the relationship is not yet established. Establish the relationship in the relationship window.

Once you have decided which way the relationship goes, create a copy of the Primary Key from the one side of the relationship and place it as Foreign Key in the many side of the relationship. You may need to do some work on the Foreign Key properties to ensure the field is repeatable in the table that is the many side of the one-to-many relationship.
You accomplish this be selecting the Primary Key with the left mouse key and then dragging the PK field to the Foreign Key field in the related table and then dropping it there. A relationship will appear in the Relationship window between the two tables. A lot of stuff is going on in the background in Access, but for our purposes, the creation of that line between the two fields establishes the relationship between them.

[top]

Referential integrity

Referential integrity is a system of rules that ensure relationships between records in related tables are valid, and that you don't accidentally delete or change related data. For two tables to retain their referential integrity (that is, so that the relationship between them remains healthy and correct), you should always enforce it.

Cascades

you can override the restrictions against deleting or changing related records and still preserve referential integrity by setting the Cascade Update Related Fields and Cascade Delete Related Records check boxes
- when the Cascade Update Related Fields check box is set, changing a primary key value in the primary table automatically updates the matching value in all related records
- when the Cascade Delete Related Records check box is set, deleting a record in the primary table deletes any related records in the related table
when you complete the editing of the relationship, your Relationship view will show you the proper relationship between the two tables

[top]

Look Up Fields

Lookup lists: Displays values looked up from a related table
Value lists: Same as a lookup list but consists of a fixed set of values you type in when the lookup is created
- A value list should only be used for values that will not change very often and don't need to be stored in a table

Creating a look up field

In Design View.... (note how this example includes descriptions of each field to help the user understand the intent of the field)

click in the desired field in the "Data Type" column
select "Look Up Wizard"
press Next (let Access decide what to lookup)

if you have the field already related as a Foreign Key, you may have to temporarily delete the relationship in the relationship window in order to run the Lookup Wizard
to check if things worked, change views to Datasheet view

Look Up Tables can be

"limited" meaning only data from the look up table can be entered, or "not limited", meaning a person can enter data that is not in the lookup table (of course this depends on the tables' relationships)

click the appropriate Field Name
under the Field Properties area, select the "Lookup" tab
for the option "Limit to List", you can select Yes or No
- for this Access Project, you want "Yes" selected

[top]