Linking tables through relationships is what transforms a collection of spreadsheets into a relational database. When tblOrders references tblCustomers through CustomerID, you can run queries that join customer names with their orders without duplicating data.

Example Tables

• tblCustomers:CustomerID (PK), FirstName, LastName, Email
• tblOrders:OrderID (PK), CustomerID (FK), OrderDate, TotalAmount
• tblOrderDetails:DetailID (PK), OrderID (FK), ProductID (FK), Quantity, UnitPrice

Create a Relationship in the Relationships Window

Click to enlarge

1. Go to Database Tools → Relationships.
2. Add tblCustomers and tblOrders to the window.
3. Drag CustomerID from tblCustomers onto CustomerID in tblOrders.
4. Select 'Enforce Referential Integrity' in the dialog.
5. Choose Cascade Update Related Fields if CustomerID values may change.
6. Click Create and save the relationship layout.

Understanding Relationship Types

• One-to-Many:One customer has many orders. This is the most common relationship in Access.
• One-to-One:Rare — used when splitting a wide table into two related tables.
• Many-to-Many:Requires a junction table (e.g., tblProductCategories linking Products and Categories).

Referential Integrity Rules

Enforcing referential integrity prevents orphaned records. Access will block you from:

• Adding an order with a CustomerID that does not exist in tblCustomers.
• Deleting a customer who still has orders (unless Cascade Delete is enabled).

Query Across Linked Tables

Once relationships exist, create a query joining tblCustomers and tblOrders to produce a customer order history report. Add indexes on all foreign key fields for faster joins — see our query optimization guide.