DCOUNT or not DCOUNT, that is the question


C

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.
 
Ad

Advertisements

R

Roger Carlson

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

Advertisements

C

Chris M.

Roger Carlson said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dcount 2
Dcount 4
Dcount 0
Dcount 4
Dcount 1
DCount 4
DCount 6
DCount with a Context 2

Top