please help with distinct count on group

[tnguyen315]

This is my query:

SELECT Establishment_Information_remote.District_number AS [District#], Inspections_remote.Establishment_code AS EstCode, Inspections_remote.Date_of_Inspection AS [Date], Inspections_remote.Inspection_type_Code AS TypeCode, Risk_Type_remote.Frequency_of_inspection AS [Insp/Year], IIf(([Inspection_type_Code]="1" Or [Inspection_type_Code]="3"),1,0) AS InsCom, Risk_Type_remote.Risk_Code
FROM (Inspections_remote INNER JOIN Establishment_Information_remote ON Inspections_remote.Establishment_code = Establishment_Information_remote.Establishment_Code) INNER JOIN Risk_Type_remote ON Establishment_Information_remote.Risk_code = Risk_Type_remote.Risk_Code
WHERE (((Inspections_remote.Date_of_Inspection) Between [Enter Begin Date:] And [Enter End Date:]))
ORDER BY Establishment_Information_remote.District_number, Inspections_remote.Establishment_code, Inspections_remote.Date_of_Inspection;


I run this query within date range, for instance from 2/1/12 to 2/3/12, I have total 6 records but only 4 establishment code count
Result:
District# EstCode Date TypeCode InsCom Risk_Code Insp/Year
1 7552 2/1/12 1 1 1 1
1 7552 2/2/12 3 1 1 1
1 7555 2/3/12 4 0 1 1
1 7557 2/1/12 6 0 2 2
1 7557 2/2/12 4 0 5 6
1 7559 2/1/12 3 1 2 2
The report i have to group by district (on this one I just show only district 1, but total I have 22 districts so each district will be showing on a new page)
My report right now: I group on District# first and then group on risk_code, so the report should be
On my district # header I have :
District # : 1
On my risk_code header I have:
#EstCount = Count([EstCode]), this shoud be DCount, but couldn’t get it work
NeededInsp = # insp/Yr X #EstCount
Comp.Insp = =Sum([InsCom])
Risk code # insp/Yr #Est Count Needed Insp Comp.Insp
1 1 3 3 2
2 2 2 4 1
5 6 1 6 0
Then under my District# footer I have the total
Grand Total Count Needed Insp = 13
Comp. Insp = 3
Insp Need to complete = 10 (Needed Insp -Comp.Insp)
On report footer I have to total count of est code = 6 (should be 4, distinct count)
The correct report should be
Risk code # insp/Yr #Est Count Needed Insp Comp.Insp
1 1 2 2 2
2 2 2 4 1
5 6 1 6 0
Then under my District# footer I have the total
Grand Total Count Needed Insp = 12
Comp. Insp = 3
Insp Need to complete = 9 (Needed Insp -Comp.Insp)
On report footer : total count of est code (no duplicate) = 4


Please help, i'm very appreciated.
Thank you very much
Twee

 

[dhookom]

You could create a totals query that groups by District and counts Establishment Code. Then add this query to your report's record source and join the District fields. You can now add the CountOfEstCode to your report in the District footer.

You can create a running sum on a similar text box to display in the report footer.

Duane
Hook'D on Access
MS Access MVP

 

[tnguyen315]

Thanks dhookom. It toook a me while to get it worked.
Again, thank you very much for your time.