Subtotals in Group Footer

  • Thread starter Thread starter Robbie Doo
  • Start date Start date
R

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?
 
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.
 
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!

Duane Hookom said:
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


Robbie Doo said:
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?
Click to expand...
Click to expand...
 
"they will always be the same" I used to believe statements like this ;-)

Happy New Year!
--
Duane Hookom
Microsoft Access MVP


Robbie Doo said:
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!

Duane Hookom said:
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


Robbie Doo said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top