Results 1 to 13 of 13
  1. #1
    hispeaches's Avatar
    hispeaches is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    12

    Help with too 5 query


    I have 1 query called Top 5. I have 3 fields in the query: amount (total $), aging (Jan 2019, Feb 2019 etc.)and payor. I am trying to build another query to show me the top 5 amounts by payor for each month. Result: 5 top values for anthem for Jan 2019, 5 top values for anthem for feb 2019 and so forth. Help! With the things I try I get the top 5 dollar amount...

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Down load the zip from RogersAccessLibrary-TopQuery
    The zip has a dB and a Word Doc. Open the Word doc and go to Page 6 "Top Query By Group"

    Should help you.....

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ack!! I looked for Allen's site first but missed it.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I always try these and sometimes I fail, but I always struggle, for sure. Each one seems different.
    This one took about 1 1/2 hours if I had to guess, but not only did I finally get it, I learned something from the exercise.
    ORDER BY IN THE SUBQUERY IS PARAMOUNT!! At least it was in this case.

    hispeaches: give it a shot because it will be a very good learning opportunity. If you're really stuck, post back, but show us what you tried, OK?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Micron View Post
    This one took about 1 1/2 hours if I had to guess, but not only did I finally get it, I learned something from the exercise.
    ORDER BY IN THE SUBQUERY IS PARAMOUNT!! At least it was in this case.
    Only an hour and a half??
    I usually struggle for at least a couple of days because I rarely use "Top Query by Group" queries. So many other things to study (SSQLE Stored Procedures at the moment). I still have a good memory... problem is that it is now really short!!

  7. #7
    hispeaches's Avatar
    hispeaches is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    12
    Hi Steve,

    I used Rogers Access Library, and the query takes forever to run and does not seem to give me the top 5 amounts for each month. What am i doing wrong? I would love to put my field payor in there as well.

    SELECT AgedAR.[Post Dt], AgedAR.Amount
    FROM AgedAR
    GROUP BY AgedAR.[Post Dt], AgedAR.Amount
    HAVING (((AgedAR.Amount) In (select top 6 [Amount] from [AgedAR] I2 where [AgedAR].[Post Dt] = I2.[Post Dt] order by I2.[Amount] desc, I2.[Payor] desc)))
    ORDER BY AgedAR.[Post Dt], AgedAR.Amount DESC;

  8. #8
    hispeaches's Avatar
    hispeaches is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    12

    screenshot of results

    Quote Originally Posted by hispeaches View Post
    Hi All,

    I used Rogers Access Library, and the query takes forever to run and does not seem to give me the top 5 amounts for each month. What am I doing wrong? I would love to put my field payor in there as well.

    SELECT AgedAR.[Post Dt], AgedAR.Amount
    FROM AgedAR
    GROUP BY AgedAR.[Post Dt], AgedAR.Amount
    HAVING (((AgedAR.Amount) In (select top 6 [Amount] from [AgedAR] I2 where [AgedAR].[Post Dt] = I2.[Post Dt] order by I2.[Amount] desc, I2.[Payor] desc)))
    ORDER BY AgedAR.[Post Dt], AgedAR.Amount DESC;
    screenshot of results..
    Click image for larger version. 

Name:	test.jpg 
Views:	13 
Size:	70.7 KB 
ID:	37736

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    [AgedAR] I2
    If this is your aliasing of the table, it doesn't look right.
    [AgedAR] AS I2 ? As for the other field you want, just add it to the first select portion?

    Using my own table/field names last night, I had this, which seems to work
    Code:
    SELECT tblTop5.payor, tblTop5.aging, tblTop5.amt
    FROM tblTop5
    GROUP BY tblTop5.ID, tblTop5.payor, tblTop5.aging, tblTop5.amt
    HAVING (((tblTop5.amt) In (SELECT TOP 5 AMT FROM tblTop5 AS T WHERE T.[payor] = tblTop5.[payor] AND T.aging = tbltop5.[aging] ORDER BY AMT DESC)));

  11. #11
    hispeaches's Avatar
    hispeaches is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    12
    Quote Originally Posted by Micron View Post
    If this is your aliasing of the table, it doesn't look right.
    [AgedAR] AS I2 ? As for the other field you want, just add it to the first select portion?

    Using my own table/field names last night, I had this, which seems to work
    Code:
    SELECT tblTop5.payor, tblTop5.aging, tblTop5.amt
    FROM tblTop5
    GROUP BY tblTop5.ID, tblTop5.payor, tblTop5.aging, tblTop5.amt
    HAVING (((tblTop5.amt) In (SELECT TOP 5 AMT FROM tblTop5 AS T WHERE T.[payor] = tblTop5.[payor] AND T.aging = tbltop5.[aging] ORDER BY AMT DESC)));
    Micron,
    Thanks so much, ur code worked!!!!!

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Fantastic! Otherwise it would have been time that I'd never get back.
    @ssanfu - the only time I seem to use subqueries (never mind grouped Top ones) is because of this forum. Me and subqueries don't seem to like each other much.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron - Agreed. Instead of sub queries, I have used a saved query and used that query as the data for the 2nd query. Grouped Top query with sub query is a real mind bender.......

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

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