By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,148 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

DCount question

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.