1 table with 2 relationships to another table

[AlexSo]

I'm putting together a database for a charity that runs a transport service. The group has members some of whom are drivers who take patients (other members) to medical appointments. I have a table for all members details and one called tblPatientJourneys to store information so that we can track payments that members pay for the journey and driver reimbursements.

However because a driver is a member he/she can also be a patient being taken to an appointment by another driver.

tblMembers

Id Name Driver
001 Mr A No
002 Mrs B Yes
003 Ms C Yes
004 Mrs D No

tblPatientJourneys



PatientID DriverID Journey
001 002 Hospital
003 002 Dentist
002 003 Surgery

Access allows the setting of relationships from one field in the members table to 2 fields in the journeys table, however things seem to become a bit unstuck from then on.
I could have a separate Drivers table, but I want to treat the drivers as ordinary members in all other ways. And some member could become a driver for a short time then revert back to being a non driver which would mean swapping the data between the two tables.

Is there any help out there? – thanking you in advance.

[jzwp11]

Since a person can play many roles, you have to associate the role the person is playing to the driving event.

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

By the way the word "name" is a reserved word in Access so it should not be used as field or table name

tblRoles (records such as patient, driver, etc.)
-pkRoleID
-txtRole

tblPeopleRoles (associate the person and roles that they may play)
-pkPeopleRoleID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRoles

Also, since you have many people associated with a journey, that would be a one-to-many relationship

tblPatientJourneys
-pkPatientJourneyID primary key, autonumber
-dteJourney (date of journey)
-JourneyReason

tblPatientJourneyPeopleRoles (for each journey you will have many people who are participating according to their role)
-pkPatientJourPeoRoleID primary key, autonumber
-fkPatientJourneyID foreign key to tblPatientJourneys
-fkPeopleRoleID foreign key to tblPeopleRoles

[AlexSo]

Thank you. Guess I need to read a good (and not too difficult) book.

[jzwp11]

You might check out Crystal's site; it has several tutorials.