Access 2003 Group By Query Issues

Posted on 2009-02-20
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

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now