Access 2003 Group By Query Issues

Posted on 2009-02-20
Medium Priority
Last Modified: 2012-05-06
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

Question by:johnnyg123
1 Comment
LVL 14

Accepted Solution

ragoran earned 2000 total points
ID: 23696205
If I understand correctly, in the table, there is one record for each second for each call. You want to know, for each hour, the maximum number of concurrent calls, e.g. calls during the same second.

Try this:

Select calldate, hour, max(CallsBySec) as MaxCalls from
(select calldate, hour, second, count(second) as CallsBySec
from callreport
group by calldate, hour, second)
group by calldate, hour

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question