Group Header Counts per Status - How? 1

[eb24]

I have an invoice report divided by regions, i.e. have 'Region Header' sections. Within that section in design mode, I have a total of records, i.e. =count(invoices). There are three types of Status that an Invoice can have: Outstanding, Pending, Completed, which are displayed in the Detail section of my report. I want to display, aside from the Total Records in the Region Header, also a stripped down of the Status of those Invoices.

For example,
Central Region
Total Records = 20
Outstanding = 10
Pending = 6
Completed = 4

Is there a way? Can anyone please assist me or direct me in a good direction? Greatly appreciative...

 

[dhookom]

If your status values will never change, you can create three text boxes with control sources like:
=Abs( Sum( [Status] = "Outstanding") )
=Abs( Sum( [Status] = "Pending") )
=Abs( Sum( [Status] = "Completed") )

A better solution would be a related subreport based on a totals query that groups by Region and Status.

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]

 

[eb24]

dhookom:

That worked perfectly! I am trying to do the same for subtotals of Invoice Amounts:

For example,
Central Region
Subtotal = $20,000
Outstanding = $10,000
Pending = $6,000
Completed = $4,000

Greatly appreciate any advice.

 

[dhookom]

Try:
=Sum( Abs( [Status] = "Outstanding") * [InvoiceAmt] )
=Sum( Abs( [Status] = "Pending") * [InvoiceAmt] )
=Sum( Abs( [Status] = "Completed") * [InvoiceAmt] )


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]

 

[eb24]

dhookom:

You're the best!! Thanks!