design question

[GeekPretender]

I'm hoping to get some ideas...

I have clients who have appointments
Client table by client id, name, address etc
Appointments by appointment id, client id, date, time, etc

These clients are provided rides TO and FROM appointments by drivers
Drivers table by driver id, name, address etc

Now the problem...
There can be 2 different drivers (to and from) for each appointment
I need to to show 2 drivers on each appointment line on the appointment
subform.
I need to keep track of mileage for each driver (not on appointment subform
but will be completed on driver/appointment subform)

What is the best way to set up?

Thanks so much in advance (by the way, I am doing this program as a
volunteer for volunteers).

 

[Allen Browne]

Is there any crucial requirement to treat these 2 trips as the same
appointment?

It seems to me that they could be treated as separate trips, and that this
would also solve those occasions where a person only wants a 1-way ride
(e.g. to a hospital, or from a family member's house.)

Depending on what you are doing, you may also need to distinguish beween
appointments (bookings/requests for a ride) and trips (actual rides given
and mileages driven.)

The Appointment/Trip table(s) would have fields:
ID primary key
ClientID person serviced
DriverID volunteer
TripDateTime date and time the trip started/is booke for.
FromLocation
ToLocation
There may also be fields for Miles or for Odometer readings.

 

[GeekPretender]

Sometimes it just takes someone to give you a fresh new way of looking at
things... this idea just might solve some other issues I have too... I will
need to ponder this idea for a bit...

Thanks muchly!

 

[Jason]

Just a quick idea. If you want to keep the "To" and "From" records related,
you could add a boolean field after the appointment id and then create a dual
key. That way both records have the same appointment id but are
differentiated by the "T" or "F". This usually works best if you set up a
form / subform, one to many relationship. Like I said, it's just an idea.