Help!!! Distinct Count within groups in the report

[jen1701]

I have a report which I need to distinct count within several groups. e.g.

JobId SN 0Defect
2345
12345aa No
12345aa No
23456bb Yes
23456bb No
So within JobId 2345, there should be 2 SN, 2 0Defect.
In order to achieve that, I grouped on SN and then grouped on 0Defect. I managed to get the distinct count of SN by using a textfield and seting the control source = 1. But I can not use the same method on 0Defect. On the textfield for 0Defect, if I set the sum on over the group, I can see the count within the 0Defect is right, but they did not add up within the JobId group. Is anybody have any ideas how to add them up.

Thank you very much in advance.

Jen

 

[dhookom]

You may need to create a totals query that calculates your values. You can then add the totals query to your report's record source to get the totals.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jen1701]

Hi Duane,

Thank you for your reply. I tried your suggestion. But the count is still over the whole report. I want to do the count within the group. I wonder you have any other suggestions.

Thanks a lot

Jen

 

[dhookom]

Can you create a totals query that calculates the values you need. This may involve creating queries based on other queries.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.