Subtotals in Group Footer

[Robbie Doo]

I have 2 grouppings: Reason and Reason Category. Each Reason has it's own
group of Categories, where I use the following to code to count:

=IIf([Dist]=26 Or [Dist]=27 Or [Dist]=40 Or [Dist]=62 Or [Dist]=7 Or
[Dist]=8 Or [Dist]=31,Count([Wdrw_Cat]),0)

The count comes out ok. However, I'm unable to Total for each Reason group
with the same formula. Can anyone help?

 

[Duane Hookom]

I question every time I see multiple values in an expression like this. Why
these Dist values? Will the collection of numbers ever vary? I would expect
this is dynamic and shouldn't be hard-coded like this. Typically I would use
a table of unique Dist values with a field that stores whether or not they
should be counted.

Next, I think you could use an expression like the following in any group or
report footer:
=Sum(Abs([Dist] IN (26, 27, 40, 62, 7, 8, 31) And Wdrw_Cat Is Not Null))

If you can't or won't add something in your tables that unique groups 26,
27, 40, 62, 7, 8, and 31 then I would probably create a small
user-defined-function that identifies these Dist values.

 

[Robbie Doo]

Thank you Duane. You always come to my rescue. Those Dist values come from a
special table for Districts. The calculation of Wdrw_Cat belongs to those
Districts and they will always be the same.

I will try your suggestion and let you know.

BTW, happy New Year!

I question every time I see multiple values in an expression like this. Why
these Dist values? Will the collection of numbers ever vary? I would expect
this is dynamic and shouldn't be hard-coded like this. Typically I would use
a table of unique Dist values with a field that stores whether or not they
should be counted.

Next, I think you could use an expression like the following in any group or
report footer:
=Sum(Abs([Dist] IN (26, 27, 40, 62, 7, 8, 31) And Wdrw_Cat Is Not Null))

If you can't or won't add something in your tables that unique groups 26,
27, 40, 62, 7, 8, and 31 then I would probably create a small
user-defined-function that identifies these Dist values.

--
Duane Hookom
Microsoft Access MVP

I have 2 grouppings: Reason and Reason Category. Each Reason has it's own
group of Categories, where I use the following to code to count:

=IIf([Dist]=26 Or [Dist]=27 Or [Dist]=40 Or [Dist]=62 Or [Dist]=7 Or
[Dist]=8 Or [Dist]=31,Count([Wdrw_Cat]),0)

The count comes out ok. However, I'm unable to Total for each Reason group
with the same formula. Can anyone help?

 

[Duane Hookom]

"they will always be the same" I used to believe statements like this ;-)

Happy New Year!
--
Duane Hookom
Microsoft Access MVP

Thank you Duane. You always come to my rescue. Those Dist values come from a
special table for Districts. The calculation of Wdrw_Cat belongs to those
Districts and they will always be the same.

I will try your suggestion and let you know.

BTW, happy New Year!

I question every time I see multiple values in an expression like this. Why
these Dist values? Will the collection of numbers ever vary? I would expect
this is dynamic and shouldn't be hard-coded like this. Typically I would use
a table of unique Dist values with a field that stores whether or not they
should be counted.

Next, I think you could use an expression like the following in any group or
report footer:
=Sum(Abs([Dist] IN (26, 27, 40, 62, 7, 8, 31) And Wdrw_Cat Is Not Null))

If you can't or won't add something in your tables that unique groups 26,
27, 40, 62, 7, 8, and 31 then I would probably create a small
user-defined-function that identifies these Dist values.

--
Duane Hookom
Microsoft Access MVP

I have 2 grouppings: Reason and Reason Category. Each Reason has it's own
group of Categories, where I use the following to code to count:

=IIf([Dist]=26 Or [Dist]=27 Or [Dist]=40 Or [Dist]=62 Or [Dist]=7 Or
[Dist]=8 Or [Dist]=31,Count([Wdrw_Cat]),0)

The count comes out ok. However, I'm unable to Total for each Reason group
with the same formula. Can anyone help?