DCount 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.
dsm2cam notat ups dot com

[deko]

For starters, I'd try using more than one query - and use a standard SELECT
Count(tblTable.Indexed_ID) AS Whatever rather than DCOUNT. You might try
using a left join to get the results you're looking for. I usually solve
these kind of problems by writing multiple queries to get the different
pieces of the puzzle, then try consolidating the queries to the most
efficient solution.
"Chris M." <ds*****@ups.com> wrote in message
news:cf**************************@posting.google.c om...

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.
dsm2cam notat ups dot com