Sum of balances for a repeating Invoice Number

[wirejp]

Referencing post: -http://bytes.com/topic/access/answer...eating-invoice
I need some help with the calculation of invoice amounts on a Microsoft Access 2010 report. My report shows the following fields; -invoice#, invoice amount, payment amount, and payment due (i.e. payment due = payment amount - invoice amount). There are some invoice numbers have balances for their different regional offices:
for example:

Invoice#  Company  Site  InvoiceAmt   PmtAmt   PmtDue
Invoice1     A          A1       2000            500        1500
Invoice1     A          A2       4000           1000        3000
Invoice 1    A          A3       1000           1000          0
Invoice 2    B           B        3000           3000          0
 

On my report, I would like to show two invoices on my report: - Company A has an Invoice1 with an Outstanding Balance $4,500 and Company B has an Invoice2 with an show total invoice amount is 0 (since it's only 2 invoices). I want to show this on my report. I was browsing around the internet and I came across a workaround method of doing this but it doesn't seem to be working for me. This is what I have done: -

I created a count query called qryPmtCount to count the number of invoice rows of each invoice based on my report's recordsource query: -

SELECT qryPayments.InvoiceNumber, Count(qryPayments.[InvoiceNumber]) AS N
FROM qryPayments
GROUP BY qryPayments.[InvoiceNumber];
 

I created another new query called "qryPmtSum" in which I joined the "qryPmtCount" query to the original report query called qryPayments on the invoice number. I added all the fields from your original report query along with the count field, N, from the count query. Afterwards, I then changed the report's recordsource from your original query (qryPayments) to this new one.

In my report, I added the new count field N to the detail line, but setted its visible property to false (because I do not want to show how many invoice payments there are for that invoice). Finally, to get the true invoice value shown on each unbound invoice total, I changed the control source to: -

=SUM([PmtDue]/[N])

But the report is still not showing the expected results of only payment amounts for two invoice numbers: - Invoice1 and Invoice2. The report is still showing 4 line items: Invoice 1 repeating three times and Invoice 2 once.

Thank you in advance for your help

[wirejp]

I got the issue resolved. Thanks

[Rabbit]

Can you post your solution in case someone runs into the same problem?

[wirejp]

Instead of using the approach described above, I used this query instead: -

SELECT qryPaymentPremiums.PremiumInvoiceNumber, qryPaymentPremiums.[ClientID], Sum(qryPaymentPremiums.NetPremiumAmt) AS SumOfNetPremiumAmt, Sum(qryPaymentPremiums.PremiumPmt) AS SumOfPremiumPmt, Sum([NetPremiumAmt]-[PremiumPmt]) AS PmtPrem
FROM qryPaymentPremiums
GROUP BY qryPaymentPremiums.PremiumInvoiceNumber, qryPaymentPremiums.[ClientID];