Problem running a query

Can anybody help
I have constructed a database that is primarily for stock tracking, invoices and customer details.

On one of my forms I have the facility to record when a customer pays for something and then comes back over a period of time to pay part payment. Each Invoice then can have a number of records for part payments. This I have successfully created. However when I want to run a report to show the total paid and the total outstanding it all goes wrong.
The invoice amount of say £100.00 is ok and then for every part payment say £10.00 the query will Deduct £10.00 from £100.00 however on the next transaction of say payment of 20.00 the query that I have created Ddt 20 from £100.00 rather than £90.00. When I group the items for a report it records each record. Therefore invoice total = £200.00 and rather than £100.00

I have created a Table for invoice payment with the following fields, ID;
amount;payment date.

Does any one have any ideas. I can provide more information if necessary . Thanks.

a little help...

I hope this helps, I'd need to know more about you structure to help more, but I'll try...

You have a table, say "tblPayments" and it has fields "[ID]","[Amount]", and "Date" right?

Somewhere else you've either entered an amount due for the client, or you are calculating a total amount due.

Further, let us imagine you have the following data in tblPayments:

ID Amount Date
--------------------------
12 $12.21 5/5/2004
77 $10.00 6/3/2004
77 $20.00 6/14/2004
77 $12.00 7/12/2004
93 $40.00 7/1/2004

You know that Client with ID 77 racked up a total bill of $100.00, and now you are sending him an invoice, here's the way to go about it:

To get how much of his $100 he still owes, you need to add up all the partial payments he's made so far. Use a domain aggregate function to do this. Here is how to do it for this case:

dsum = ("[Amount]", "tblPayments", "[ID] = '77'")

that statement will return a value of $42.00 in our case, then all you have to do is subtract that from $100, and you are set.

For your invoice report I suggest you do it this way...

You make your invoice report have a control source that is a recordset (query) that has the id field, we'll call it "[ClientId]" in it. Then in the appropriate place put control. For its label put "Amount already paid (total)".
In its control source put

=dsum("[Amount]", "tblPayments", "[ID] = '" & [ClientId] & "'")

this way, it will calculate the proper amount for whatever ID you pass to it, via the invoices report's recordsource.

If you ran a report for ID 12, then this field would report $12.21, for ID 93 it would report $40.00.

Also, if you wanted, you could report the number of partial payments by changing the word "dsum" to "dcount" - similarly, you could report the average of the partial payments with "davg".

Does any of this help? You can write me back if you think I can help.

My email is rogertheroman@yahoo.com

Sorry about the $!!!


ComputerRaj
Roger H.
Plano, Texas

Since you should have a OneToMany relationship between invoice and payments, you should use a Report and subReport. The subReport would be linked by the InvoiceID and show payment details for each invoice. There's no point in summing the payments at query level, it can all be done on the report.

a little help again...

What I meant was, if he needs to generate not one invoice report, but one for each client, develop a query that returns a recordset of all clients, use that as a recordsource for the mass_invoice report, then on the report itself, have a field that uses a domain aggregate function to sum up partial payments for each client.

Also, not being a great fan of subreports, if he didn't need to display each partial payment, he need not use one. Even if he does need to display each partial payment, he can just make the mass_invoice report's recordsource include id and partial payments, and diplay them that way, although he would need to go into 'sorting and grouping' in the report and select 'date'.


ComputerRaj

domain aggregate functions are slow, if he used a subReport as suggested he wouldn't need DSum etc at all.
It's irrelevant whether the report is for one or many customers, the subReport works fine for both.

I guess you haven't discovered the power of subReports