Available Dates

[cbayardo]

Hi, I have a table called tblEmployees. This table has Name and Phone etc. I
want to create another table that has available dates and times, but I have a
bit of a block in my head. Any ideas would be appreciated. This 2 tables
should be linked together and display the information on a form.
Thanks for your help

 

[John W. Vinson]

Hi, I have a table called tblEmployees. This table has Name and Phone etc. I
want to create another table that has available dates and times, but I have a
bit of a block in my head. Any ideas would be appreciated. This 2 tables
should be linked together and display the information on a form.
Thanks for your help

Please explain what it means to be "available". I presume that there'd be a
start time and end time - but do you mean that Joe is available from 10am to
11:30am every day? Every Tuesday? On August 28? Will there be multiple
"available" slots or just one?

My guess would be that you would have a table with three fields: EmployeeID
(link to the primary key of tblEmployees), StartTime and EndTime; this could
have multiple records and could be searched or displayed in a subform.

John W. Vinson [MVP]

 

[cbayardo]

Availability would be from Sunday to Saturday and the specific times. For
Example:
Employee John is Available to work on Monday from 8-5, Wednesday from 8-5,
and Friday from 8-2. Peter is available only on Saturday from 5am to 11:59pm,
etc.
Thanks

 

[John W. Vinson]

Availability would be from Sunday to Saturday and the specific times. For
Example:
Employee John is Available to work on Monday from 8-5, Wednesday from 8-5,
and Friday from 8-2. Peter is available only on Saturday from 5am to 11:59pm,

This will be a bit complex since Access date/time fields are real dates and
times - "Monday 8am" isn't a specific date/time, since there are quite a few
Mondays between January 1, 100AD and December 31, 9999AD!

I would suggest a table with fields:

Availability
EmployeeID << link to Employees primary key
DayAvailable <integer, valid range 1 through 7, day of the week, 1=Sunday>
StartTime <Date/Time>
EndTime <Date/Time>

If someone is available across midnight you'll need two records: say employee
123 is available from 8pm Monday through 4am Tuesday you'ld have

123 2 20:00:00 23:59:59
123 3 00:00:00 04:00:00

You'll need a moderately complex query to link this to a specific date/time to
see who's available on #8/25/2007 15:00:00# but it could certainly be done.

John W. Vinson [MVP]