Access DB - Multiple Time/Date for one ID

Question

I'm creating a small db for a non-profit where volunteers drive disabled/elderly citizens to different locations around the area. They currently use Excel sheets to update everything manually and want to move to something more streamlined. I have one table of drivers that has all their information, however there is a field that requires multiple time/date fields where they are not available to drive. Such as Joe Schmoe is not available for volunteering Sun 6AM-1PM, Mon 3PM-6PM, Wed 6AM-3PM.

What is the best way to go out about dealing with multiple time/dates like this? I eventually want to be able to query available drivers from Time X to Time Y.

Answer 1

create a table to keep track of schedules

driver id, start_time, end_time

put an index on driver id, but don't make it a primary key

Answer 2

You should have a separate table when multiple values are needed. The new table would have say,

DriverID          ) You can either use these two fields as the index, or use
NotAvailableFrom  ) another, autonumber field. There are arguments for both.
NotAvailableTo

You can then relate this table to the main table on DriverID. When you set up your form, Drivers would be in the main form and the Not Available table would be a subform.

You might like to read http://www.r937.com/relational.html