Adding Sub-Totals in Report Footer

[BrianFawcett]

I currently am designing a report that has for a report footer a count of records and a sum of the $ for the records.

=Count([Date Received])
=Sum([Old Hardsales])

I want to created subtotals on each of these field by the status of the records: NEW, PENDING, CLOSED.

Right now the report is grouped by Sales Organization so this counts and totals are not obvious.

How do I write the text boxes to create these totals?

 

[pbaldy]

Try

=Sum(IIf(Status = "New", [Old Hardsales], 0))

 

[BrianFawcett]

Paul, I just tried that and when I viewed the report Access asked me for a parameter value for Old Hardsale.

 

[pbaldy]

I copied that from your working Sum(). It needs to be the actual name of the field in the data that you want to sum.

 

[BrianFawcett]

I made a typo, thanks again.

 

[pbaldy]

Happy to help.

 

[BrianFawcett]

Paul, I just tried the same thing on the count function and it gave me the total count for all 3 status. Will this not work for Count.


=Count(IIf([Status]="New",[Date Received],0))

 

[pbaldy]

For a count, you can do this:

=Sum(IIf(Status = "New", 1, 0))

 

[JTHIEL]

pbaldy,

Just a note to thank you - I just solved a similar problem using your advice - 2.5 years later! Thanks! Joan

 

[pbaldy]

Happy to help Joan, and welcome to the site!