Linking Tables with Relationships

A relationship in Access is a formal link between two tables, defined by a matching field — the primary key in one table (the 'one' side) and a foreign key in another table (the 'many' side):

• One-to-Many: one patient can have many appointments, but each appointment belongs to exactly one patient. The Patients table (PatientID is the primary key) is the 'one' side. The Appointments table (PatientID is the foreign key) is the 'many' side. This is the most common relationship type in a medical office database.
• Foreign key setup: the PatientID field in the Appointments table must have the same data type as the PatientID primary key in the Patients table. Both should be Number (Long Integer) if PatientID is an AutoNumber in Patients. The field names do not need to be identical, but they typically are for clarity.
• To open the Relationships view: click Database Tools > Relationships. This shows a visual diagram of all relationships in your database. Drag each table from the Show Table dialog into the diagram, then drag the primary key field from the 'one' table to the foreign key field in the 'many' table to create the relationship line.

Referential integrity is the rule that enforces valid relationships — it prevents orphaned records and ensures every foreign key value matches a real primary key value in the related table:

• Enabling referential integrity: when you create a relationship line in the Relationships view, Access shows a dialog. Check the 'Enforce Referential Integrity' checkbox. With this checked, Access will: (1) prevent creating an appointment record with a PatientID that does not exist in the Patients table, and (2) prevent deleting a patient record that still has related appointment records.
• Cascade Update Related Fields: if enabled, changing a patient's PatientID in the Patients table automatically updates all matching PatientID values in the Appointments table. For AutoNumber primary keys, IDs never change, so this option is rarely needed — but useful for natural keys that might change.
• Cascade Delete Related Records: if enabled, deleting a patient record automatically deletes all related appointment records. This is powerful but dangerous in a medical context — enabling cascade delete means one accidental patient deletion removes all their appointment history. Leave this unchecked in a healthcare database and instead manage deletions manually with appropriate authorization.

Once relationships are defined, you can create queries that pull data from multiple related tables simultaneously — answering questions that span the entire database:

• A multi-table query in Access automatically recognizes the relationship and joins the tables correctly — when you add both the Patients table and the Appointments table to a Query Design, Access draws the relationship line between them. You can then select fields from both tables: patient name from Patients and appointment date from Appointments. The query returns one row per appointment, with the correct patient name filled in from the Patients table.
• Inner joins return only records that have matches in both tables — the default join type. A query joining Patients and Appointments with an inner join returns only patients who have at least one appointment, and only appointments that are linked to a real patient. Patients with no appointments are excluded.
• Left outer joins return all records from the left table even if there is no match in the right table — useful for finding patients who have not had any appointments (the appointment fields show as blank for those patients). Change the join type by double-clicking the relationship line in Query Design view.