Invoice Report

[Phil H]

Hi,
I have created an invoice report that under the detail section shows the
Product Name, Size, colour and cost etc. My customers have the choice to make
stage payments at different times and the balance on the invoice gets updated
each time.

This all seems to work ok for one payment but if I enter a second payment
the above fields are duplicated on the invoice. I can't just hide duplicates
in case my customer orders two products of the same colour etc.

I assume that the error is between my payments table and the invoice. Has
anyone got any ideas I can look at.

Many Thanks in advanve
Phil H

 

[Allen Browne]

Very familiar. If you have 2 payments for one invoice, and you join the
invoices and payments table together in a query, then the invoice will be
listed twice. Then if you sum the invoice amount from that query, the amount
is in the total twice.

To avoid that, you need to remove the payments table from the query, so each
invoice is listed once only. Then if you need to list the payments for each
invoice, you could do that in a subreport.

Alternatively, if you just need the amount of payments received in your
invoice, you could use a subquery. That's a complete SELECT query statement
within your query. You would type something like this into the Field row of
the query that as the tblInvoice table but not the tblPayment table:
( SELECT Sum(PaymentAmount) FROM tblPayment
WHERE tblPayment.InvoiceID = tblInvoice.PaymentID)

More compete subquery:
PaymentsReceived: CCur(Nz(( SELECT Sum(PaymentAmount)
FROM tblPayment
WHERE tblPayment.InvoiceID = tblInvoice.PaymentID), 0))

More on subqueries:
http://support.microsoft.com/?id=209066

More on why the more complete statement is suggested:
http://members.iinet.net.au/~allenbrowne/ser-45.html

 

[Phil H]

Thanks Allen,
That was very useful, I removed the payments table from the query and all
worked great.

I have used as suggested the subreport to list payments made, which is ok
for what I want at the moment. I may try your other way later to see how that
looks but this is all new stuff to me and that way seemed a little more
complicated.

Thanks for your help
Phil H

 

[SMC, the Amateur]

Allen, You said the problem below was "Very Familiar". Is there a "Dummies"
sort of book that helps with familiar/common Access problems?

I have "Troubleshooting Microsoft Access 2002" Which I've found extremely
helpful over the years but over those same years I couldn't find the answer
to this problem.

Any hard copy resources you would recommend? I like a book in my hand.
Still fume each time I purchase new software/hardware with *digital* manuals.
I understand about the trees but I like boundpaper!