Top Counts

[DemanBOA]

To anyone out there...
I have a query where I count the number of Buyers for a specific Auction.
Now in my report, i now need to get the top 5 buyers from each of these 20
auctions. My report is already grouping by "Auction" and "BuyerID" and Would
some one let me know how i can get to this end result. Thanks!

 

[John Spencer]

SELECT BuyerID, AuctionID
FROM SomeTable
WHERE BUYERID IN
(SELECT TOP 5 BuyerID
FROM SomeTable as Temp
WHERE Temp.AuctionID = SomeTable.AuctionID
ORDER BY SomeField Desc)

Some field is whatever you are using to determine the top 5 buyers.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[DemanBOA]

John,
Thank you for the quick response. Would you happen to know if there is a way
of performing this same function in Design View of Access?
Thank you, David

 

[John Spencer]

Well, you can build the select query in design view, but the Criteria
subquery clause will have to be manually entered. The under the BuyerID
field you would have to type
(SELECT TOP 5 ... FROM ...)

IF you can't figure out how to build the subquery in the criteria, post your
current SQL (Menu View:SQL) and perhaps I or someone else can suggest the
necessary modification.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..