Time Off Requests: querying multiple time off requests per day per employee to figure out schedule

[nrv]

Hopefully I can explain this well....

I have 3 Tables I need to query from:

Agents (which houses employees names and reporting numbers)
SchedulewithDates (Which includes employees normal shifts by date)
PTO Schedule (Which links to a Sharepoint list where employees enter their time off requests)

So, what I need to do is look at those tables to determine what the actual schedule is. Currently, I am pulling the normal shifts in my query and then using SQL to pull in the time off categories, times and dates (from the PTO Schedule table). Here is the problem... Often times people have more than one request per day. Sometimes they leave and come back for multiple doctors appointments, and we also include meetings and training in the PTO Schedule. Each request is it's own record in the PTO Schedule table. I need a way to pull in all of their time off requests for that day so then I can have it determine what each individual employees schedule for that day will be and so that I can count how many agents we have here per hour. We do customer service so it is important for us to know how many people we have taking calls each hour. I can pull all of their PTO requests with a query, but I'm not sure how to make them all list as one record in my query which also includes their normal schedule (SchedulewithDates) and then calculates the their correct scheduled time.

I have been mulling this over and I'm just hitting a wall figuring out how to do it.

[NTC]

well it is complex. first you need to establish the most granular time period...perhaps an hour - then you must generate a record for each person for each period they are scheduled within a day. that record perhaps is just a check box, date, period. then you need to generate an 'uncheck' for each leave request.

then you can count

there is of course no way around multiple duplicate or overlapping requests - these must be cleaned out or prevented from being entered in the first place....