Tuesday, 24 March 2015

Relationships Exercises in Access



After you've set up different tables for each subject in your Microsoft Access database you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships.

Relationships: An association that is established between common fields (columns) in two tables. A relationship can be one to one, one to many or many to many.

A relationship works by matching data in key fields, a field with the same name in both tables. In most cases these matching fields are the primary key from one table which provides a unique identifier for each record and a foreign key in another table.

A one-to-many relationship is the most common type of relationship. 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

Example One

  1. Identify the primary key in each table by a *
  2. Identify the foreign key in the order table by underlining it 
  3. State the relationship you can create......................................................
  4. Draw the relationship between the two tables


Customer


Order

 Customer ID

OrderID
Name
CustomerID
Address
ItemCode
ContactNumber
Description
ContactName
Quantity
 
Example Two

  1. Identify the primary key in each table by a *
  2. Identify the foreign key in the order table by underlining it 
  3. State the relationship you can create......................................................
  4. Draw the relationship between the two tables


Patient


Appointments

PatientId

ApptID
PatientName
PatientID
PatientAddress
Day
DOB
Time



Example Three

Expand on example two; patients must book to see a Dr

  1. Identify the primary key in each table by a *
  2. Identify the foreign key in the order table by underlining it 
  3. State the relationship you can create......................................................
  4. Draw the relationship between the three tables

Patient


Appointment

Dr


PatientId
ApptID
DrID
PatientName
DrID
DrName
PatientAddress
PatientID

DOB
Day


Time



Propellerads