Distinct Count in Group Footer

[mtownbound]

Trying to get a distinct count total in the footer of each of my Category groups. I know about:

Name: txtCategory
Control Source: =1
Running Sum: Over All
Visible: No

Name: txtTotalCategories
Control Source: =txtCategory
Running Sum: No
Visible: Yes

However, I need to capture the count for each group, not just the total in the report footer. Placing txtTotalCategories in the group footer isn't yielding what I'm after. Any ideas?


Thanks!!

 

[dhookom]

What happens when you change the Running Sum: Over All to Over Group?

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

It's adding the value of the employee group footer with the last value of the previous category group. Let me try to illustrate according to how I have them grouped:


Employee 1
Hats 1 txtCategory Value: 1
Shoes 1 txtCategory Value: 2
Shirts 1 txtCategory Value: 3
Employee 1 Footer: txtTotalCategories Value: 1 Desired txtTotalCategories Value: 3

Employee 2
Hats 1 txtCategory Value: 1
Shoes 2 txtCategory Value: 2
Shirts 7 txtCategory Value: 3
Socks 8 txtCategory Value: 4
Pants 3 txtCategory Value: 5
Employee 2 Footer: txtTotalCategories Value: 6 Desired txtTotalCategories Value: 5

Employee 3
Hats 3 txtCategory Value: 1
Shoes 4 txtCategory Value: 2
Shirts 5 txtCategory Value: 3
Gloves 1 txtCategory Value: 4
Employee 3 Footer: txtTotalCategories Value: 10 Desired txtTotalCategories Value: 4

 

[dhookom]

Did you change to Over Group? If it was Over All, the numbers would count up from 1 to 12.

I can't get my test to display like you are suggesting.

Another solution is to create multiple group by queries to get your counts and then add these to the record source of your report.

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

I'll try the multiple group by queries but you would think that if you can add a distinct count to the report footer then you should be able to do the same for a group footer with just a simple field move.

 

[mtownbound]

I tried both over group and over all, but neither produced the desired outcome of resetting the count with each group instead of counting up from 1 to 12.

 

[dhookom]

You stated

Name: txtTotalCategories
Control Source: =txtCategory
Running Sum: No
Visible: Yes

but it really seems the Running Sum is not set to No.


Duane
Hook'D on Access
MS Access MVP