Count(Distinct()) with multiple fields

[S Davis]

I realize my last post revealed too much information and made it
confusing. Let's start from scratch

I would like to assemble a query that counts distinct values.

Here is my count(disctint()) query thus far:

*****
SELECT Count([EngKMFC-KMREADINGS2].OBJ_CODE) AS Expr1
FROM [SELECT distinct OBJ_CODE FROM [EngKMFC-KMREADINGS2]]. AS ABC;
*****
(Apologies for my table name )

Now what I want to do is add in the following, all from the same table:

REA_DATES -- between 2006-09-24 and 2006-12-23
OBJ_MRC
OBJ_CATEGORY
REA_DIFF -- Sum

And that's it! My problem is I don't understand how to incorporate
those 4 fields into the initial query so that the
count(distinct(OBJ_CODE)) is the excluding factor, REA_DATES selects
the range of dates to pull data from, and REA_DIFF is a sum of the
distinct OBJ_CODE's within that date range.

For instance, if this is my raw data:

OBJ_CODE OBJ_MRC OBJ_CATEGORY REA_DIFF REA_DATES
2101 176 1 100 2006-09-24
2101 176 1 200 2006-09-24
2101 177 1 300 2006-09-24
2102 177 2 400 2006-09-24
2103 178 3 500 2006-09-24
2103 178 3 600 2006-09-24

I would like to see this as output:

Count(Distinct OBJ_CODE) OBJ_MRC OBJ_CATEGORY Sum(REA_DIFF)
2 176 1 300
1 177 1 300
1 177 2 400
2 178 3 1100

The ouput above would exclude any information outside of a desired date
range.

Thanks so much!

 

[Smartin]

I realize my last post revealed too much information and made it
confusing. Let's start from scratch

I would like to assemble a query that counts distinct values.

Here is my count(disctint()) query thus far:

*****
SELECT Count([EngKMFC-KMREADINGS2].OBJ_CODE) AS Expr1
FROM [SELECT distinct OBJ_CODE FROM [EngKMFC-KMREADINGS2]]. AS ABC;
*****
(Apologies for my table name )

Now what I want to do is add in the following, all from the same table:

REA_DATES -- between 2006-09-24 and 2006-12-23
OBJ_MRC
OBJ_CATEGORY
REA_DIFF -- Sum

And that's it! My problem is I don't understand how to incorporate
those 4 fields into the initial query so that the
count(distinct(OBJ_CODE)) is the excluding factor, REA_DATES selects
the range of dates to pull data from, and REA_DIFF is a sum of the
distinct OBJ_CODE's within that date range.

For instance, if this is my raw data:

OBJ_CODE OBJ_MRC OBJ_CATEGORY REA_DIFF REA_DATES
2101 176 1 100 2006-09-24
2101 176 1 200 2006-09-24
2101 177 1 300 2006-09-24
2102 177 2 400 2006-09-24
2103 178 3 500 2006-09-24
2103 178 3 600 2006-09-24

I would like to see this as output:

Count(Distinct OBJ_CODE) OBJ_MRC OBJ_CATEGORY Sum(REA_DIFF)
2 176 1 300
1 177 1 300
1 177 2 400
2 178 3 1100

The ouput above would exclude any information outside of a desired date
range.

Thanks so much!

Methinks all you need is an aggregate query:

SELECT
Count([EngKMFC-KMREADINGS2].OBJ_CODE) AS CountOfOBJ_CODE,
[EngKMFC-KMREADINGS2].OBJ_MRC,
[EngKMFC-KMREADINGS2].OBJ_CATEGORY,
Sum([EngKMFC-KMREADINGS2].REA_DIFF) AS SumOfREA_DIFF
FROM
[EngKMFC-KMREADINGS2]
WHERE
[EngKMFC-KMREADINGS2].REA_DATES Between #9/24/2006# And #12/23/2006#
GROUP BY
[EngKMFC-KMREADINGS2].OBJ_MRC,
[EngKMFC-KMREADINGS2].OBJ_CATEGORY;