Lecture 4: One-to-Many Relationship & Lookup Wizard

1. Deep Dive: One-to-Many (1:M)

This is the most common relationship type.

• Example Scenario: A Customers table and an Orders table.
  • Customers (PK: ID)
  • Orders (PK: ID, FK: Customer_ID)
• Logic: We want to assign a specific customer to every order placed.

2. Using the Lookup Wizard

Instead of manually typing the Customer ID into the Orders table (which causes errors), Access provides the Lookup Wizard. It creates a dropdown menu in the Foreign Key field that pulls data from the Primary Key table.

3. Practical Implementation Steps

1. Prepare Tables: Ensure Customers table is populated. Create the Orders table in Design View.
2. Select Foreign Key: In the Orders table, go to the Customer_ID field.
3. Data Type Selection: Instead of selecting 'Number', select Lookup Wizard from the dropdown list.
4. Wizard Steps:
   • Step 1: Choose "I want the lookup field to get the values from another table or query." -> Next.
   • Step 2: Select the source table (Customers). -> Next.
   • Step 3: Select the fields you want to see in the dropdown (e.g., ID, Customer_Name). -> Next.
   • Step 4: Sort order (optional). -> Next.
   • Step 5: Hide Key Column (recommended to keep checked usually, but ensure you know which is the bound column). -> Next.
   • Step 6: Enable Data Integrity: Check "Enable Data Integrity". This prevents "orphan records" (orders without valid customers). You can choose "Restrict Delete" (prevents deleting a customer who has orders).
5. Finish: Save the table. Access will ask to create relationships; click Yes.

4. Verification

• Go to Database Tools -> Relationships.
• You will see a relationship line connecting Customers (1 side) to Orders (infinity/many side).
• Open the Orders table in Datasheet view; the Customer_ID field will now be a dropdown list showing customer names.