Please help!
I am trying to accomplish 2 distinct counts for two fields in 1 query. Is
this possible? I would appreciate the SQL view accomplishing this.

I currently run 2 queries,
1) grouping by Reg & HH then counting Pol & summing Amt then run
2) grouping by Reg, count HH, sum polcount and amt

For example, my data is as follows 4 fields, Reg, HH, Pol, Amt

Reg HH Pol Amt
NE Bill 2 $10
SW Jane 8 $12
NE Bill 7 $5
SC Rob 3 $50
NE Tim 9 $5
SW Jill 1 $5

The end result of my query would count within each region the distinct # of
HH and # of policies and sum up the amounts
Should be as follows

Reg DistinctHH CountofPol SumofAmt
NE 2 3 $20
SW 2 2 $17
SC 1 1 $50

jennifer wrote:
>I am trying to accomplish 2 distinct counts for two fields in 1 query. Is
>this possible? I would appreciate the SQL view accomplishing this.
>
>I currently run 2 queries,
>1) grouping by Reg & HH then counting Pol & summing Amt then run
>2) grouping by Reg, count HH, sum polcount and amt
>
>For example, my data is as follows 4 fields, Reg, HH, Pol, Amt
>
>Reg HH Pol Amt
>NE Bill 2 $10
>SW Jane 8 $12
>NE Bill 7 $5
>SC Rob 3 $50
>NE Tim 9 $5
>SW Jill 1 $5
>
>The end result of my query would count within each region the distinct # of
>HH and # of policies and sum up the amounts
>Should be as follows
>
>Reg DistinctHH CountofPol SumofAmt
>NE 2 3 $20
>SW 2 2 $17
>SC 1 1 $50


I think you do need two queries to do that.

First create a query to get the distinct HH:
qryDistinctHH:
SELECT DISTINCT Reg, HH
FROM thetable

Then this query will provide the desired results
SELECT T.Reg,
Y.DistinctHH,
Count(*) AS CountOfPol,
Sum(T.Amt) AS SumOfAmt
FROM thetable As T INNER JOIN
[SELECT X.Reg, Count(*) As DistinctPol
FROM qryDistinctHH As X
GROUP BY X.Reg]. Y
ON Y.Reg = T.Reg
GROUP BY T.Reg, Y.DistinctHH

(Note the dot after the square bracket)

--
Marsh
MVP [MS Access]

Marshall -

I'm getting an error: my DISTINCT query is called qry_WorkProjects

qry_TimeReportProjects-------------------------------------------------------------------
SELECT DISTINCT WorkProject, WorkHours, Employee, WorkMonth
FROM tbl_TimeReport INNER JOIN tbl_TimeDetails ON
tbl_TimeReport.TimeReportID = tbl_TimeDetails.TimeReportID
WHERE (((tbl_TimeReport.TimeReportEmployee) Like [Enter employee's full
name]) AND ((tbl_TimeReport.TimeReportMonth) Like [For what month?]))
ORDER BY tbl_TimeDetails.WorkProject;

produces (pretty much):
**NOTE: results are not DISTINCT = problem #1

WorkProject WorkHours
ProjectA 4
ProjectA 5
ProjectA 6
ProjectA 10
ProjectB 4
ProjectB 8

qry_WorkProjectHours---------------------------------------------------------------------
SELECT T.WorkProject, Y.QRY_TimeReportProjects, Sum(T.WorkHours) AS
SumOfWorkHours
FROM tbl_TimeDetails As T INNER JOIN
[SELECT X.WorkProject
FROM QRY_TimeReportProjects As X
GROUP BY X.WorkProject]. Y
ON Y.WorkProject = T.WorkProject
GROUP BY T.WorkProject, Y.QRY_TimeReportProjects

-- so, as expected via qry_WorkProjects, I am asked to enter the employee's
name and the month. however then I am asked to enter a value for
Y.QRY_TimeReportProjects. I throw in "*" to return all and it works, but why
am I getting this? Any ideas?


Thanks for the code here!
/amelia
> I think you do need two queries to do that.
>
> First create a query to get the distinct HH:
> qryDistinctHH:
> SELECT DISTINCT Reg, HH
> FROM thetable
>
> Then this query will provide the desired results
> SELECT T.Reg,
> Y.DistinctHH,
> Count(*) AS CountOfPol,
> Sum(T.Amt) AS SumOfAmt
> FROM thetable As T INNER JOIN
> [SELECT X.Reg, Count(*) As DistinctPol
> FROM qryDistinctHH As X
> GROUP BY X.Reg]. Y
> ON Y.Reg = T.Reg
> GROUP BY T.Reg, Y.DistinctHH
>
> (Note the dot after the square bracket)

aaearhart wrote:
>I'm getting an error: my DISTINCT query is called qry_WorkProjects
>
>qry_TimeReportProjects-------------------------------------------------------------------
>SELECT DISTINCT WorkProject, WorkHours, Employee, WorkMonth
> FROM tbl_TimeReport INNER JOIN tbl_TimeDetails ON
>tbl_TimeReport.TimeReportID = tbl_TimeDetails.TimeReportID
> WHERE (((tbl_TimeReport.TimeReportEmployee) Like [Enter employee's full
>name]) AND ((tbl_TimeReport.TimeReportMonth) Like [For what month?]))
>ORDER BY tbl_TimeDetails.WorkProject;
>
>produces (pretty much):
>**NOTE: results are not DISTINCT = problem #1
>
>WorkProject WorkHours
>ProjectA 4
>ProjectA 5
>ProjectA 6
>ProjectA 10
>ProjectB 4
>ProjectB 8
>
>qry_WorkProjectHours---------------------------------------------------------------------
>SELECT T.WorkProject, Y.QRY_TimeReportProjects, Sum(T.WorkHours) AS
>SumOfWorkHours
>FROM tbl_TimeDetails As T INNER JOIN
> [SELECT X.WorkProject
> FROM QRY_TimeReportProjects As X
> GROUP BY X.WorkProject]. Y
> ON Y.WorkProject = T.WorkProject
>GROUP BY T.WorkProject, Y.QRY_TimeReportProjects
>
>-- so, as expected via qry_WorkProjects, I am asked to enter the employee's
>name and the month. however then I am asked to enter a value for
>Y.QRY_TimeReportProjects. I throw in "*" to return all and it works, but why
>am I getting this? Any ideas?
>
>
>> I think you do need two queries to do that.
>>
>> First create a query to get the distinct HH:
>> qryDistinctHH:
>> SELECT DISTINCT Reg, HH
>> FROM thetable
>>
>> Then this query will provide the desired results
>> SELECT T.Reg,
>> Y.DistinctHH,
>> Count(*) AS CountOfPol,
>> Sum(T.Amt) AS SumOfAmt
>> FROM thetable As T INNER JOIN
>> [SELECT X.Reg, Count(*) As DistinctPol
>> FROM qryDistinctHH As X
>> GROUP BY X.Reg]. Y
>> ON Y.Reg = T.Reg
>> GROUP BY T.Reg, Y.DistinctHH
>>
>> (Note the dot after the square bracket)


Something got mangled in the translation from your original
post to my reply and on into your last attempt ;-)

I think problem #1 is because you included WorkMonth in the
field list (with its Show box checked). I can't tell for
sure because you didn't paste all the results, but that's
what it looks like from here. It's unclear why you are
using the Like operator (instead of =) on the WorkMonth
criteria. Also note that an Order By clause not only won't
serve any useful purpose in theis query, but it will also
make the query run slower.

qry_TimeReportProjects-------------------------------------------------------------------
SELECT DISTINCT WorkProject, WorkHours
FROM tbl_TimeReport INNER JOIN tbl_TimeDetails
ON tbl_TimeReport.TimeReportID =
tbl_TimeDetails.TimeReportID
WHERE (tbl_TimeReport.TimeReportEmployee =
[Enter employee's full name])
AND (tbl_TimeReport.TimeReportMonth =
[For what month?])

qry_WorkProjectHours---------------------------------------------------------------------
SELECT T.WorkProject,
Y.DistinctPol,
Sum(T.WorkHours) AS SumOfWorkHours
FROM tbl_TimeDetails As T INNER JOIN
[SELECT X.WorkProject,
Count(*) As DistinctPol
FROM QRY_TimeReportProjects As X
GROUP BY X.WorkProject]. Y
ON Y.WorkProject = T.WorkProject
GROUP BY T.WorkProject, Y.DistinctPol

--
Marsh
MVP [MS Access]