Group total in distinct field

[Ray]

I have a report with following Sorting and Grouping.

Sorting and Grouping

By - Ascending - Group Header & Footer
supplier - Ascending - Group Header only
SuppliersModel - Ascending - Group Header & Footer

I need to count distinct numbers of supplier and supplier model on the By
group footer. I tried to create a group header each for the supplier and
suppliermodel fields. Add a text box each in the group header:

Name: txtSupplier or txtSupplierModel
Control Source: =1
Running Sum: Over Group
Visible: No

Add two text boxes to the By footer
Control Source: =txtSupplier
Control Source: =txtSupplierModel

It works correctly on total supplier but not total supplier model as below:-

Total supplier Total suppliers model
9 (Correct) 4 (incorrect) 16 (Should be)
7 (Correct) 1 (incorrect) 12 (Should be)

Can someone give me some guidance where is the error.

Thanks,

Ray

 

[Marshall Barton]

I have a report with following Sorting and Grouping.

Sorting and Grouping

By - Ascending - Group Header & Footer
supplier - Ascending - Group Header only
SuppliersModel - Ascending - Group Header & Footer

I need to count distinct numbers of supplier and supplier model on the By
group footer. I tried to create a group header each for the supplier and
suppliermodel fields. Add a text box each in the group header:

Name: txtSupplier or txtSupplierModel
Control Source: =1
Running Sum: Over Group
Visible: No

Add two text boxes to the By footer
Control Source: =txtSupplier
Control Source: =txtSupplierModel

It works correctly on total supplier but not total supplier model as below:-

Total supplier Total suppliers model
9 (Correct) 4 (incorrect) 16 (Should be)
7 (Correct) 1 (incorrect) 12 (Should be)

You're sooo close Ray.

You just need to add another text box (name it
txtRunSupplierModel to the Total Supplier group footer to
accumulate the model subtotals. Set its expression to
=txtSupplierModel and RunningSum to Over Group.

Then the By footer can refer to txtRunSupplierModel to get
the model total per By.

 

[Ray]

Marshall,

Following your instructions, it works beautifully. Thanks a million! Could
you please tell me how it works or point me out somewhere I can get greater
explanation as I am likely to do more group totals.

Thanks,

Ray

 

[Marshall Barton]

Marshall,

Following your instructions, it works beautifully. Thanks a million! Could
you please tell me how it works or point me out somewhere I can get greater
explanation as I am likely to do more group totals.

The pattern here is that you put the Over Group running sum
text boxes at the level you want to total **for the next
level up** . So you'd use a running sum text box in the
detail section to get the total for the model group footer.
Then place another in the model group footer to total for
the supplier group footer. And so on, up through the levels.

Of course, when you want grand totals for the report footer,
you can use Over All running sum text boxes at any level.
--
Marsh
MVP [MS Access]

 

[Ray]

Marshall,

Thanks for your enlightenment.

Ray

The pattern here is that you put the Over Group running sum
text boxes at the level you want to total **for the next
level up** . So you'd use a running sum text box in the
detail section to get the total for the model group footer.
Then place another in the model group footer to total for
the supplier group footer. And so on, up through the levels.

Of course, when you want grand totals for the report footer,
you can use Over All running sum text boxes at any level.