Totaling on a report

[eskybel]

Hey everyone,

I know this should be easy, but I'm just not getting what I want.

I have a table, a query, and a report. The query gets records based on To/From dates. The query pulls data that looks like this:

Date	TOTAL SAVINGS	Status
3/6/2013	$2,500.00	100
3/21/2013	$1,000.00	50
3/28/2013	$2,000.00	75

If status is 100, it's considered "closed" and if the status is less than 100, it's considered "open."

What I want is to have a report that will look like this:

Company Name
From Date: 1/1/13 (whatever is typed)
To Date: 3/31/13 (whatever is typed)

Open Status: 2 Savings: $3,000
Closed Status: 1 Savings: $2,500
______________________________________
Grand Total: 3 Savings: $5,500

I have the query working fine based on prompts for To/From dates, and the report shows the entered dates fine. Where I'm stuck is filtering based on "status" and totaling each.

I made groups for Open Header/Footer, and Closed Header/Footer, and I used a =Count(*) to get the counts for each group, then a =Count(*) for the total, but it's just not doing what I want. I don't want a line on the report for every record that is returned. I just want a total of everything returned, broken out by either "open" or "closed" and a total of records and savings for each group, then a grand total.

Is VBA code a better place to achieve this? I can't seem to make it do what I want in the query, using Iifs, and displaying properly on the report.

TIA,
Adam

[pbaldy]

Try a textbox like:

=Sum(IIf(Status < 100, Savings, 0))

using the actual field names.

[eskybel]

Thanks for that. How do I go about getting the counts of records for each category, then totaling, and having it look like the above example for the report?

[pbaldy]

One way:

=Sum(IIf(Status < 100, 1, 0))

Another option would be a subreport based on a totals query with the same criteria.

[eskybel]

When using the above formula, I get #Error. If I do without Sum, I will get one line that's "0" and one line that's "1". If I make a second text box and tell it to sum those original controls, I also get #Error.

I put these text boxes in the Page Header section, because putting it anywhere else, makes a line for each record in the table, on the report, which I do not want.

If I put =Count(*) at the Footer, I will get a total of the records, which is one thing I need, other places it #Errors. The location is not ideal.

[pbaldy]

Sum won't work in a page footer, only a group or report footer.

[eskybel]

But doing a sum in those areas gives me a line on my report for each record in the query, which I'm not looking for. Is there a way to get a subtotal and total count for each category without making the report huge? I'm not interested in seeing the actual records, just the totals.

Maybe I'm misunderstanding, or not explaining it right? Included a picture for reference. Top portion is design, bottom is print preview. It's very close, but I'm getting two "Opens" and two "Closes" when I want one of each.

Thanks!

Date	TOTAL SAVINGS	Status
3/6/2013	$2,500.00	100
3/21/2013	$1,000.00	50
3/28/2013	$2,000.00	75

[pbaldy]

Have you tried the report footer? Can you post the db here?

[eskybel]

That was it. I swore I tried exactly that and it gave me errors, but I put the Open/Closed in the report footer and it's working appropriately now.
Thanks for everything!

[pbaldy]

Happy to help!