DCOUNT or not DCOUNT, that is the question

[Chris M.]

I'm trying to pull the top two people in multiple locations using
DCOUNT. If there is a tie for 2nd, my query only shows one person.

Table: tblSales-Location, EEID, Units, etc...

Query:
Select Location, EEID, Units, Dcount("[EEID]","tblSales","[Location]_
=" & [Location] & " AND [Units]>=" & [Units]) AS Rank _
FROM tblSales

Data:
Location EEID Units
1 123 25
1 456 12
1 789 10
2 147 19
2 258 13
2 369 13

Results:
Location EEID Units Rank
1 123 25 1
1 456 12 2
1 789 10 3
2 147 19 1
2 258 13 3
2 369 13 3

If I use <3 as the criteria in the Rank field, I only get one record
for Location 2. I need to see all the records that come in the next
group after Rank 1.


Help! TIA

Chris M.

 

[Roger Carlson]

On my website (see sig below) is a small sample database called
"TopQuery.mdb" which deals with duplicates in Top queries.

 

[Chris M.]

On my website (see sig below) is a small sample database called
"TopQuery.mdb" which deals with duplicates in Top queries.

That my friend, was the perfect answer. I didn't want to have to run
a different query for each location, and this worked great.

Thanks a bunch!

Chris M.