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

2 distinct count in one query

P: n/a
I'm trying to write a query that will select a distinct count of more
than one field. I have records that display user productivity. Each of
the records have a time associated with it and I want to be able to
tell the distinct count of the products numbers and of the pallets ID
that they worked with. Do need to work out some sort of sub-query to do
this?

Dec 27 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Bill wrote:
I'm trying to write a query that will select a distinct count of more
than one field. I have records that display user productivity. Each of
the records have a time associated with it and I want to be able to
tell the distinct count of the products numbers and of the pallets ID
that they worked with. Do need to work out some sort of sub-query to do
this?
Subqueries for counts can often be avoided by using the IIf function.
In your case that won't work because of your need to find a distinct
count. Here's my first try at it:

tblProductivity
PID AutoNumber
UserID Long
ProductNumber Text
PalletID Long
PID UserID ProductNumber PalletID
1 3 RS3DF 2
2 3 RS3DF 2
3 3 SS4DF 2
4 4 TS5DF 4
5 4 TS5DF 5
6 5 US6DG 6

qryDistinctPallets:
SELECT DISTINCT UserID, PalletID, 1 AS ToSum, (SELECT Count(A.PalletID)
FROM tblProductivity AS A WHERE A.UserID = tblProductivity.UserID AND
A.PalletID = tblProductivity.PalletID) AS PalletCount FROM
tblProductivity;

!qryDistinctPallets:
UserID PalletID ToSum PalletCount
3 2 1 3
4 4 1 1
4 5 1 1
5 6 1 1

qryDistinctProducts:
SELECT DISTINCT UserID, ProductNumber, 1 AS ToSum, (SELECT
Count(A.ProductNumber) FROM tblProductivity AS A WHERE A.UserID =
tblProductivity.UserID AND A.ProductNumber =
tblProductivity.ProductNumber) AS ProdCount FROM tblProductivity;

!qryDistinctProducts:
UserID ProductNumber ToSum ProdCount
3 RS3DF 1 2
3 SS4DF 1 1
4 TS5DF 1 2
5 US6DG 1 1

qryPalletAndProductCountsByUser:
SELECT UserID, (SELECT Sum(A.ToSum) FROM qryDistinctPallets AS A WHERE
A.UserID = qryDistinctProducts.UserID) AS PalletCount, (SELECT
Sum(A.ToSum) FROM qryDistinctProducts AS A WHERE A.UserID =
qryDistinctProducts.UserID) AS ProductCount FROM qryDistinctProducts
GROUP BY UserID;

!qryPalletAndProductCountsByUser:
UserID PalletCount ProductCount
3 1 2
4 2 1
5 1 1

Maybe that will suffice until maybe I see an elegant way to do this. I
think some versions of Access later than A97 are better at combining
distinct queries with subqueries so perhaps someone can suggest an
elegant solution that doesn't have the limitations of A97.

James A. Fortune
CD********@FortuneJames.com

Dec 28 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.