468,305 Members | 1,463 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,305 developers. It's quick & easy.

DCount question

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
Nov 13 '05 #1
1 1684
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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Megan | last post: by
2 posts views Thread by Paul T. RONG | last post: by
2 posts views Thread by solar | last post: by
8 posts views Thread by Susan Bricker | last post: by
5 posts views Thread by Wayne | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.