Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Unanswered: Sort on count of records

    Hello, can't seem to get this one. Need to sort report of grouped events by the number of events. So:

    Event 1
    4 records
    Event 3
    3 records
    Event 2
    2 records
    Event 4
    1 record

    Is this possible? Thanks, Scott

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Do you mean something like:
    Code:
    SELECT Tbl_Events.Event_ID, COUNT(Tbl_Events.Event_ID) AS CountID
        FROM Tbl_Events
        GROUP BY Tbl_Events.Event_ID
        ORDER BY COUNT(Tbl_Events.Event_ID);
    Have a nice day!

  3. #3
    Join Date
    Mar 2013
    Posts
    70

    Sort on Count of Records

    First, if this is a duplicate post please excuse me, I thought I had sent a reply but it does not appear here.

    Here is the SQL from my query that works THANKS TO YOU!

    Code:
    SELECT Count(tblCalls.Location) AS CountID, tblCallLocations.callLocation
    FROM tblCallLocations INNER JOIN tblCalls ON tblCallLocations.callLocationID = tblCalls.Location
    GROUP BY tblCallLocations.callLocation, tblCalls.FA
    HAVING (((tblCalls.FA)=Yes))
    ORDER BY Count(tblCalls.Location) DESC , tblCallLocations.callLocation;
    ANY additional fields I add to the query grid breaks the query. The above SQL gives me a result of a CountID column with a total of the count for each location and the location. So, for 77 total records I get 44 aggreagated records, with the first having a CountID of 11 and the next having a countID of 8 and so on. This is great and it makes the report work. When I add ANY other fields to the query, the query changes to a list of 77 records each with a count of 1. That also breaks the report. Any idea what I am missing or doing wrong? Best, Scott

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    This seems normal as GROUP BY groups identical rows (i.e. sets of columns). If you add a column in the SELECT part of the query expression, you must also add it to the GROUP BY part (unless an aggregate function: SUM, COUNT, MIN, MAX, AVG, etc. is used in the SELECT part). Now, the rows that were identical for column X are not identical for column X, Y.
    Have a nice day!

  5. #5
    Join Date
    Mar 2013
    Posts
    70
    Hi, thanks for the response. If I understand you correctly, then the select statement creates a recordset and the group by must equal the select recordset?

    So I added another field into the sql in both the select statement and the group by statement. That broke the query in the same way. I also tried adding it into the order by statement as well and that did not help either.

    So I must not really understand your response. Is there indeed away to do this or does your response indicate to me that this is not a possibility? I certainly appreciate all your time and patience on this. Best, Scott

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Consider this table:
    Code:
    Row number | Id | Some data
    -----------+----+----------
         1     |  1 |    x
    -----------+----+----------
         2     |  1 |    y
    -----------+----+----------
         2     |  2 |    z
    -----------+----+----------
    This query:
    Code:
    SELECT Id, Count(Id) As CountId
        FROM TableName
        GROUP BY Id
        ORDER BY Count(Id);
    Yields:
    Code:
    Id  | CountId
    ----+-----------
    2   |   1
    ----+-----------
    1   |   2
    This is correct (fortunately!) as there are 2 different values for the column Id in the table.

    Now if you want to add the column [Some data] to the query, you must specify it both in the SELECT part and in the GROUP BY part. If you don't do so, Access (more precisely the Jet engine) will raise an error: "The expression [Some data] is not part of the aggregate function". So you must use:
    Code:
    SELECT Id, [Some data], Count(Id) AS CountId
        FROM TableName
        GROUP BY Id, [Some data]
        ORDER BY Count(Id);
    This yields:
    Code:
    Id | Some data | CountId
    ---+-----------+--------
    2  |   z       |  1
    ---+-----------+--------
    1  |   y       |  1
    ---+-----------+--------
    1  |   x       |  1
    Because there are 3 possibilities (3 rows) of different combined values of Id and [Some data].

    Depending on precisely what you want to do, it would perhaps be possible to use a subquery to get the results you're looking for. Unfortunately, I don't know enough of your project to provide a more precise answer.

    As you mentioned that the target of the query is a report, please also consider using the grouping and sorting functions that are built in a report object. They are powerful and could provide an easy way out.
    Have a nice day!

  7. #7
    Join Date
    Mar 2013
    Posts
    70

    Sort on count of records

    I'm sorry to be so long in replying.

    I think I understand what you are saying about adding a field. I have an id and a location that's records can be counted. I think you are saying that adding another field will force Access to compare all three fields to make a new row. My third field is always unique. Currently I have an id and an address with several different dates that may or may not have a check box selected for each row. The query selects for the check box being yes. Adding the dates is the problem.

    tbl source for query:
    callID - unique - autonumber
    Run # - number - unique
    Run Date - date - dups ok
    Location - number (from a lookup table) -dups ok

    So is there a way to work around the date issue. I would like to have a line that shows the address with total count of records that meet the check box yes. Under that or next to that I would like to list the dates for each address.

    As you showed me, this is the current SQL that works without the dates:
    Code:
    SELECT Count(tblCalls.Location) AS CountID, tblCallLocations.callLocation, tblCalls.FA
    FROM tblCallLocations INNER JOIN tblCalls ON tblCallLocations.callLocationID = tblCalls.Location
    GROUP BY tblCallLocations.callLocation, tblCalls.FA
    HAVING (((tblCalls.FA)=Yes))
    ORDER BY Count(tblCalls.Location) DESC , tblCallLocations.callLocation;
    If I understand your answer above, the following should add the dates:
    Code:
    SELECT Count(tblCalls.Location) AS CountID, tblCallLocations.callLocation, tblCalls.[Run Date], tblCalls.FA
    FROM tblCallLocations INNER JOIN tblCalls ON tblCallLocations.callLocationID = tblCalls.Location
    GROUP BY tblCallLocations.callLocation, tblCalls.[Run Date], tblCalls.FA
    HAVING (((tblCalls.FA)=Yes))
    ORDER BY Count(tblCalls.Location) DESC , tblCallLocations.callLocation;
    This causes the query to revert to no totals in the CountID field. So I wentto the report but no permutation of grouping and/or sorting I use in the report seems to remedy this. The report is currently grouped on callLocation ASC (w/heared) and sorted by CountID smallest to largest (no header or footer). It seems to me that this should work, but it does not accurately sort the report. It sorts on callLocation and seems to ignore the CountID sort.

    I am probably just being dense, but I cannot imagine that this is impossible. Frustrated. Thanks, Scott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •