I cannot tell you everything you need to know because I don't know that I know it, either, but here are some topics that might spur your imagination if you read up on the help files and think about it a bit.
You probably need at least three tables to do this. Try this concept. Break the week into "slots" that have arbitrary numbers. Say, for example, that your appoint times are every half hour from 8:00 AM to 5:00 PM from Monday through Friday. Now assign a number to each slot. Monday at 8 AM is slot 1. This would make Friday at 4:30 PM slot 90 (if I counted it right.)
Now, table 1: tblWorkers (your workers)
-- worker ID (primary key, numeric)
-- worker name, rank, other data
Table 2: tblWrkSched (your workers' basic availability)
-- worker ID (numeric, relation to tblWorkers as many to one)
-- slotID
-- maximum contiguous slots (see later for multi-slot discussion)
(Option: Build a Slot Translation table for display purposes only, so that when building displays and reports, you don't have to recompute something every time, you just look it up with a JOIN. I'm not a purist about recomputing things just because I can. Sometimes it is easier on your code requirements to have little lookup tables for translations.)
Optional Table 2a: tblSchedDsply
-- slotID
-- day name, e.g. "Tue"
-- start-time for the slot in text format, e.g. "09:30"
Table 2 entries represent this situation: If worker 2345 accepts folks on Monday at 8 AM, then an entry for (2345,1) is in the table. If worker 2345 does NOT accept folks at 9 AM, then an entry for (2345, 3) does NOT exist. To allocate lunch or off days for this person, just don't make an entry.
Table 3: tblApptmnt
-- date
-- slot
-- worker ID
-- person holding the appointment
-- number of slots consumed by appointment (default 1)
(Now, if you have a "Clients" table, then the person holding the appointment can be identified by their client number.)
OK, to USE this stuff, look at the DatePart function as a way to find out the day of the week so you can compute the slot number. The slot number according to the above scheme might be to first determine the weekday using DatePart, then determine the time in minutes using DateDiff, then divide that by 30 (because we said 30 minute slots). From these two things you can compute the slot number.
Now it is a matter to do queries.
You can make an appointment for that Client to see a Worker if:
1. the DCount of appointments for that date, slot number, and worker is 0, and
2. the DCount of slots in the Schedule table for that slot number and worker is 1.
If the number of slots consumed is not 1, then you might have to change the first query from a specific slot number to a slot number between (initial slot number) and (initial slot number + number of slots - 1), and of course you would have to sanity check for picking a slot number/number of slots combo that would cross from 5 PM to 8 AM of the next day. But those are easy to find if you include a (maximum contiguous slots) field in the worker schedule table, then make sure that the 4:30 PM slot on each day has a (max contiguous) of 1.
Now this is nowhere near a complete solution, but this might be enough to get you started thinking along these lines. And by the way, if you don't like 30-minute slots, change them to whatever they need to be. Same with start times, etc. This is just a concept discussion anyway.
Good luck! Schedulers ain't easy.
