468,539 Members | 1,496 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

Sum of balances for a repeating Invoice Number

75 64KB
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:
Expand|Select|Wrap|Line Numbers
  1. Invoice#  Company  Site  InvoiceAmt   PmtAmt   PmtDue
  2. Invoice1     A          A1       2000            500        1500
  3. Invoice1     A          A2       4000           1000        3000
  4. Invoice 1    A          A3       1000           1000          0
  5. Invoice 2    B           B        3000           3000          0
  6.  
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: -
Expand|Select|Wrap|Line Numbers
  1. SELECT qryPayments.InvoiceNumber, Count(qryPayments.[InvoiceNumber]) AS N
  2. FROM qryPayments
  3. GROUP BY qryPayments.[InvoiceNumber];
  4.  
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: -
Expand|Select|Wrap|Line Numbers
  1. =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
Sep 1 '15 #1

✓ answered by wirejp

Instead of using the approach described above, I used this query instead: -
Expand|Select|Wrap|Line Numbers
  1. SELECT qryPaymentPremiums.PremiumInvoiceNumber, qryPaymentPremiums.[ClientID], Sum(qryPaymentPremiums.NetPremiumAmt) AS SumOfNetPremiumAmt, Sum(qryPaymentPremiums.PremiumPmt) AS SumOfPremiumPmt, Sum([NetPremiumAmt]-[PremiumPmt]) AS PmtPrem
  2. FROM qryPaymentPremiums
  3. GROUP BY qryPaymentPremiums.PremiumInvoiceNumber, qryPaymentPremiums.[ClientID];

3 1089
wirejp
75 64KB
I got the issue resolved. Thanks
Sep 1 '15 #2
Rabbit
12,513 Expert Mod 8TB
Can you post your solution in case someone runs into the same problem?
Sep 2 '15 #3
wirejp
75 64KB
Instead of using the approach described above, I used this query instead: -
Expand|Select|Wrap|Line Numbers
  1. SELECT qryPaymentPremiums.PremiumInvoiceNumber, qryPaymentPremiums.[ClientID], Sum(qryPaymentPremiums.NetPremiumAmt) AS SumOfNetPremiumAmt, Sum(qryPaymentPremiums.PremiumPmt) AS SumOfPremiumPmt, Sum([NetPremiumAmt]-[PremiumPmt]) AS PmtPrem
  2. FROM qryPaymentPremiums
  3. GROUP BY qryPaymentPremiums.PremiumInvoiceNumber, qryPaymentPremiums.[ClientID];
Sep 2 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

21 posts views Thread by palanidharma | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.