Hourly time difference between start and stop time for each hour

[dudeitsme]

Hello.

I'm trying to calculate how many employees we have scheduled each hour. An example would be if an employee works from 6am to 330pm I would like the report/crosstab query to look something like this:

6am 7am 8am....3pm
1 1 1 .5 (.5 since the employee is not working the full hour)

I was able to modify the following code to fit my needs. The issue I'm having is that if an employee only works half an hour it still calculates it as a full hour. All shifts start of end on quarter hour increments. (00, 15, 30, 45)



The example below is from 6am-7am, and then I have a query for each hour through 8pm-9pm.

1000: Sum(IIf((Hour([ztblSchedules_3_22_19]![Start_Time])*60+Minute([ztblSchedules_3_22_19]![Start_Time])<=600) And ((Hour([ztblSchedules_3_22_19]![End_Time])*60+Minute([ztblSchedules_3_22_19]![End_Time]))>=660),1,0))

[CJ_London]

sounds like you want to calculate the man hours per hour?

lots of questions you need to answer and would help to see the full query and some realistic example data (10-15 rows) and provide some explanation of your field names

1. are the start and end times those on the employee? or the hour?
2. what does the 1000 indicate?
3. Is the .5 used for any part of an hour or do you want .25 or .75 if their shift finishes on the quarter hour.
4. Are all start/end times actually on quarter hour increments or are they rounded up/down?
5. Are the start/end times just times or do they include the date as well

[dudeitsme]

Yes, I'm trying to calculate scheduled man hours each hour.

1. Start and End times are the employees scheduled time.
2. 1000 is the label for the 10am - 11am hour
3. Yes, I do need .25, .5, and .75 depending when the employee schedule ends.
4. Everything is in quarter hour increments so rounding isn't needed.
5. The tables are linked to a SharePoint list so all of the times have date included, but start and end time dates always match.

[CJ_London]

OK, can you provide some example data - 40-50 records in an access table. I don't have time to generate it myself.

Also can you explain what the title data means

0600-1500 - looks like this is what the start and end time is derive from? or the other way round?
what does MTHFSASU represent? records 4 and 5 have the same reference, different time - so is this one person or two?

[dudeitsme]

Here's an example of how I currently have it set up.

The Title column represents the schedule and the days of the week the agent will be working.
The current set up for the tracking hours doesn't allow me to create a query to distinguish how many work hours are used by day.

Records 4 and 5 are different schedules that can be assigned to an agent. I have a form with all the agents names on it, and a combo box to assign the schedule. Several employees can have the same schedule.

[CJ_London]

OK so just to be clear, each row represents an employee so you just want a simple rowcount plus adjustment for part hours - it is not to be split by agent?

[dudeitsme]

Correct. I don't need individual hours for each agent, just the total hours scheduled for that day by hour.

[CJ_London]

you have a number of issues with your data


1. nonsense records - records 58 and 59
2. you have other records with a time element but a different title structure - rexcords 56 and 57
3. you have shifts that start at 10pm and finish the following day
4. your time fields are not just time, they include a date element which does not reflect the fact the end date is the following day

this query ignores all these records

TRANSFORM Count([Counter])/4 AS Expr1
SELECT DateValue([start_time]) AS Expr2
FROM ztblSchedules_3_22_19, admCount
WHERE (((admCount.[counter])>=CInt(([Start_Time]-3)*24*4) And (admCount.[counter])<CInt(([End_Time]-3)*24*4)) AND ((ztblSchedules_3_22_19.Title) Not In ("None","-")) AND ((ztblSchedules_3_22_19.End_Time)>[Start_Time]))
GROUP BY DateValue([start_time])
PIVOT ([Counter]\4)

to make this work you need to create this table with the name admCounter


and this query which you should save as admCount

SELECT CLng([singles].[ctr]+([tens].[ctr]*10)) AS [Counter]
FROM admCounter AS singles, admCounter AS tens
ORDER BY CLng([singles].[ctr]+([tens].[ctr]*10));

the result of the query based on the data provided is

Expr2	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22
02/01/1900	9	21	31.5	37.75	40.5	41	42	48.75	50	41	27.75	19	15	11	11	9.5	0.75