Access 2003 Group By Query Issues
Posted on 2009-02-20
Below is a sample of some data from a callreport table in an Ms Access 2003 Database.
CallDate Second Hour
11/1/2008 32818 9
11/1/2008 34000 9
11/1/2008 82818 23
11/1/2008 84000 23
11/1/2008 84020 23
The entire table contains data for the previous 12 months but for simplicity sake I just took a few records.
What I am trying to accomplish is to write a query which will list the maximum concurrent calls that occurred for a given hour on a given day. I'll try and explain my thought process in trying to accomplish this. (I am wide open to possible suggestions)
The table data was generated via some code I wrote which processed a record set generated form a query against another table which had much more call detail info. The "Master" table had the call date/time along with the call duration (in seconds)
The code striped the time information from the call datetime and put that in the
call date field. Then the code took the hour (in military time) and put the hour number value field in the hour field. The code calculated which second of the day the call datetime evaluated to (out of the possible 86,400 un a day) and put that in the second field. It writes a record for each second of the call duration for a given call
My thought is that once we had call date, hour and second, then some how I could create a query that would give the max calls for any given hour.
I hope I have explained this well enough