Group and Sum

[mykebass]

Hi. I have a report in which I group by Date and Market and I would like, on the same report at the end, sum the totals of only the markets. For example the report is currently like this:

Group 1:
Date: 3-27-05
Market: Chicago 500.00
Market: Denver 250.00
Market NY 100.00

Group 2:
Date: 3-20-05
Market: Chicago 200.00


Now, the values are calculated based on criteria within the report, so I can't simply group the underlying query to get the results I need. Instead, i'm hoping to find a way to place the following info at the end of the report:

Market: Chicago: 700.00
Market: Denver 250.00
Market NY 100.00

Any help would be greatly appreciated

 

[PaulBricker]

In the Report footer, you would simply add textboxes and use an expression like this

=Sum(IIf(Market = "Chicago",[CurrencyFieldToSum],0))

Then you would just adjust which market you are summing in each textbox.


Paul

 

[mykebass]

I've actually tried this, but I get prompted for the parameter value of the field I'm attempting to sum on the report.

 

[CosmoKramer]

If these values are calculated controls you must re-calculate them in the Sum statement in your report footer.

As an example, if that value was derived by (Price + SalesTax), the statement for Chicago's text box should look something like this:

=Sum(IIf([Market]="Chicago",[Price]+[SalesTax],0))

I'm CosmoKramer, and I approve this message.

 

[dhookom]

What do you mean by "calculated based on criteria within the report"?

I would find a way to do this with a subreport based on a total query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]