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.