Class Session: Wednesday Nov 07, 2018
Use a copy of the PK from the one side of a relationship as a FK in the many side of a relationship. It is possible to do this either manually, or by using the lookup wizard to modify the properties of the FK in the table itself.
We will use the lookup wizard method. Do not use this lecture notes page to step through the process for setting relationships for task 5.02. Follow the rubric directions on the task 5.02 page.
A relationship requires the primary key of one table be inserted as a foreign key in a second table.
To view/create relationships in Access, select Database Tools > Relationships (or select the Relationships icon on the toolbar).
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. This setup is hardly ever worth the effort. In most cases it causes a system performance overload. However, in some cases, this setup can actually improve performance. This is not information that you need to know about for this course. If you are interested in understanding more about one-to-one relationship setup and how it affects data, check out one-to-one relationships at databaseprimer.com.
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.
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)
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
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.
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 you complete the editing of the relationship, your Relationship view will show you the proper relationship between the two tables.
Display values looked up from a related table
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
In Design View.... (note how this example includes descriptions of each field to help the user understand the intent of the field).
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.
For this Access Project, you want "Yes" selected