Count of Groups 1

[maxhugen]

I have a schema: Contractors -> Invoices -> InvoiceRejections (one Invoice can have multiple Rejections). I'm trying to report on the number of Invoices and Invoice Rejections by Contractor.

In a report, I have grouped on Contractor and Invoice. I'm getting the count of Rejections correctly, but I'm struggling to get the correct count of Invoices.

I have tried adding a hidden text box 'InvoiceCount' with Source =1, and a running sum Over Group, plus a text box 'ContractorInvoiceCount' in the Contractor header, source =[InvCount]. However, the text box 'ContractorInvoiceCount' always displays 1.

I also tried placing 'ContractorInvoiceCount' in the Contractor footer, but same result.

The report should look like this:
[tt] Number Number
Invoices Rejections
====================================================================
Jacksons Plumbing 3 2
====================================================================
Invoice: JP08-1938 $3,987 2
----------------------------------------------------------------
Rejected: Missing Work Order
Rejected: Missing Unconditional Release
----------------------------------------------------------------
Invoice: JP08-1942 $1,105 0
----------------------------------------------------------------
Invoice: JP08-1967 $6,788 0
====================================================================
Pacific Electrical 2 5
====================================================================
Invoice: 099543 $13,050 2
----------------------------------------------------------------
Rejected: Missing Work Order
Rejected: Missing Asbestos statement
----------------------------------------------------------------
Invoice: 099589 $14,190 3
----------------------------------------------------------------
Rejected: Missing Work Order
Rejected: Missing Unconditional Release
Rejected: Missing Asbestos statement[/tt]

Can anyone suggest how to best accomplish this pls?

Max Hugen
Australia

 

[dhookom]

I would create one or more totals queries that calculate the "number of Invoices and Invoice Rejections by Contractor". These should have one record per contractor. Add these queries to your reports record source and join the contractor fields. Add the total columns to your report field list.

Duane
Hook'D on Access
MS Access MVP

 

[maxhugen]

Thanks Duane, I'll try it that way.

Max Hugen
Australia