assuming my interpretation in my previous post is correct, this query works and produces the same result as the spreadsheet
1. create a table with one column and populate with the number 1-1440 (the number of minutes in a day) -or use my flexible counter query at the end of this post
2. then use this query
Code:
SELECT C.aDate, Count(C.worked) AS CountOfworked
FROM (SELECT DISTINCT DateValue([F1]) AS aDate, 1 AS worked, admCount.Counter
FROM Table1 AS R, admCount
WHERE (((admCount.Counter) Between DateDiff("n", DateValue([F1]),[f1]) And DateDiff("n", DateValue([F1]),[f2])-1))) AS C
GROUP BY C.aDate;
admCount is the table/query of numbers, Table1 is just the TimeIn, Timeout datestamp columns from the excel spreadsheet
The DISTINCT query simply records whether one or more tickets were being worked on that particular minute and if so populates with 1 - which is then summed in the outer query by date
aDate |
CountOfworked |
11/03/2019 |
209 |
12/03/2019 |
169 |
with regards over midnight - that works but the time will be put to the day in which the minute occured - so a ticket running from 11:30pm-12:15am will show 30 mins in the first day and 15 in the second.
Note I included a -1 in the criteria - this was to match the spreadsheet which treats 4:30pm-4:45pm as 15 minutes - depends on the time recording but it can be argued that it is 16 minutes.
the flexible counter query
1. create a table with one field and populate with 0-9 (table called admcounter, field called ctr)
2. then create this query and save as admCount
Code:
SELECT CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000) AS [Counter]
FROM admCounter AS tens, admCounter AS singles, admCounter AS hundreds, admCounter AS thousands
ORDER BY CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000);