Results 1 to 2 of 2
  1. #1
    Fabdav is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    22

    Query with max value and max count

    I have the following sql statement
    SELECT ST.L, ST.N, Sum([C]*[U]) AS TOT, ST.A
    FROM ST
    GROUP BY ST.L, ST.N, ST.A, ST.U, Len([Z])
    HAVING (((ST.U)>0) AND ((Len([Z]))=8));



    the output is :

    L N TOT A
    6001821 81016 20 2347414
    6001821 81149 224 2384685
    6001821 81149 52 2436894
    6001821 81149 130 2566079
    6001821 81149 84 2596974
    6001821 81202 107 2433693
    6001821 81202 35 2555029
    6001821 81202 974 2604224

    I need to have:
    L MAXTOT Count N
    6001821 81202 81149

    where maxtot is the N which has the max TOT value (974 in this case)
    and Cont n in the N which has the greater number of recorrencies ( 4 times in this case)

    Any idea?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    2 queries

    1:
    SELECT Fabdav.L, Fabdav.N, Fabdav.TOT, Fabdav.A
    FROM Fabdav
    WHERE ((Fabdav.TOT)=(select max(tot) from fabdav));

    2:
    SELECT Max(CountOfN) AS MaxOfCountOfN
    FROM (SELECT Count(fabdav.N) AS CountOfN, fabdav.L, fabdav.N
    FROM fabdav
    GROUP BY fabdav.N, fabdav.L, fabdav.N)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query to count records
    By jpkeller55 in forum Access
    Replies: 4
    Last Post: 10-24-2010, 09:24 AM
  2. count query
    By lmp101010 in forum Access
    Replies: 6
    Last Post: 08-02-2010, 02:31 PM
  3. count query
    By lmp101010 in forum Queries
    Replies: 1
    Last Post: 08-01-2010, 12:20 PM
  4. Running Count Query
    By monkey2003 in forum Queries
    Replies: 0
    Last Post: 09-21-2009, 12:24 PM
  5. Result of Count Query not known elsewhere
    By Carole in forum Access
    Replies: 1
    Last Post: 09-07-2008, 09:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums