This post follows from the post: Creating & using a “lookup table” in Access. In that exercise, we created a table with the details of some Learners, tblLearners, and a table with a list of unique suburb names, tblSuburbs. We then used tblSuburbs as the Row Source for the Suburbs field Combo Box in tblLearners.
In this post:
Required knowledge:
1. Referential Integrity
This approach introduces an interesting problem: what happens if you delete a record from the Suburbs table where the specific Suburb deleted was actually being used as the suburb value for one of the records in tblLearners? Doing this would result in orphan records in the tblLearners table which would, in turn, mean your data is incomplete. To prevent this from happening, modern relational databases implement referential integrity.
2. Partner up
To enforce referential integrity, we must first create a relationship between two tables:
- Select the Database Tools menu tab
- Click on the Relationships button
- Click on the Show Table button
- Add the tblLearners and tblSuburbs tables
3. Create a relationship
The relationship is created by linking the tables using one field from each table:
- The fields MUST have the same Data Type.
- The fields do NOT have to have the same names.
Left-click-drag the suburbID field in tblSuburbs and drop it onto the Suburb field in tblLearners. The Edit Relationships dialog window will pop up.
4. Enforce Referential Integrity
Check the Enforce Referential Integrity checkbox and click the Create button. The screenshot below now shows the relationship between the two tables. The digit 1 (one) and the ∞ symbol (many) are to indicate that this is a one-to-many relationship between the tblSuburbs and tblLearners tables. This relationship describes the real-world scenario: a suburb only appears one time in the tblSuburbs table while it can appear many times in the tblLearners table — a suburb only exists once in reality — and many learners can live in the same suburb.
4. Test
Ensure that the suburbs have been selected for the learners in tblLearners and then attempt to delete a record for a suburb that is in use from tblSuburbs, the lookup table:
5. Display related records
Open the lookup table, tblSuburbs, and notice that the relationship is further demonstrated with the expand/collapse button to the left of each record which displays related records from tblLearners. How cool is that?
