Access 2003, count records per hour
I need to count how many calls per hour. My table has 3 fields... Date, Time, and Origin (see screenshot of sample data). I need to know how many In calls per hour per day, and how many Out calls per hour per day. After that, I need to sum up all that data (it's 30 days worth) and get the average number of In calls per hour, and average number of Out calls per hour. So, I'll end up with a report that shows all 30 days, with data that looks like this:
6/13/2018 IncomingPerHour 20
6/13/2018 OutgoingPerHour 12
6/14/2018 IncomingPerHour 23
6/14/2018 OutgoingPerHour 7
etc.
Screenshot of my source table:
6/13/2018 IncomingPerHour 20
6/13/2018 OutgoingPerHour 12
6/14/2018 IncomingPerHour 23
6/14/2018 OutgoingPerHour 7
etc.
Screenshot of my source table:
ASKER
Running first query, all good. Running second query, it asked me to enter a parameter value for RecCount. I noticed a leading space there so i removed it, but that didn't fix the error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much.
You're welcome.
Select [Date], Format([Time], "hh") AS Hourly, Origin, Count(*) As CallsPerHour
From your table
Group By [Date], Format([Time], "hh"),Origin
qryAvgCallsByHour
Select [Date], Origin, Avg( RecCount) As AvgCallsPerHour
From qryCallsByHour
Group By [Date], Origin