Access Query: Sort by name, then amount, then top 3 amount values

Hello,

I have built a query to extract information from several reports. The information is appended to several columns. Ultimately, I want to sort by the name and then amount, revealing the top 3 amounts per name and hiding the rest.

My query sorts by name then amount, but I am unsure of the third step. Any help would be appreciated, thank you!
LVL 1
Phil_ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
one way is to do something like this, where you join the query to itself on Name and Amount then count the number of records in the 2nd query that are >= the current amount.

Something like:

SELECT q1.Name, q1.Amount, Count(q2.Amount) as Rank
FROM yourQuery Q1
LEFT JOIN yourQuery Q2
ON Q1.Name = Q2.Name and Q1.Amount <= Q2.Amount
Group by Q1.Name, Q1.Amount
ORDER BY Q1.Name, Q1.Amount Desc
Having Count(q2.Amount) <= 3
0
Phil_ConsultantAuthor Commented:
Hello,

Thanks for your help. I'll be honest, I didn't fully understand your solution nor could I achieve the desired result with that method. However, I decided to go the "newb" route and build additional queries to sort the information then display only 3 each prior to appending to the main report. Ultimately, I'm getting the desired output with more queries than I probably should have. Thanks for your answer!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Would be glad to help you understand what I was trying to accomplish, if you would like.  Since you didn't use my solution there is no need to award me points.

The fact that you achieved your results using another method is sufficient to accept your own post as the solution.  If you would like those points back, just click the "request attention" hyperlink at the lower left corner of your original post and indicate what you want to do.
0
Phil_ConsultantAuthor Commented:
I was unable to obtain the output per the other users solution, however I believe he was on the right track or I could have misinterpreted the answer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.