Update Table Field with Sum Query

[fegarci]

This is my problem:
Table Invoice:
InvoiceId
CustomerID
Total_Invioce (Double)
Table Payments:
PaymentId
PaymentType
...
So, one payment can distributed in more than one invoice, and payments might be partial.

I created a table InvoiceVsPayment:
InvoiceID
PaymentID
PaymentPart: stores the portion of the payment that belongs to the invoice.

I have a sub-form(datasheet view) used to input the parts of the payments by Invoice. The source of the subform is a query that shows the InvoiceId, Total_invoice, and Payment part. The query works fine letting me update the paymentpart, however, I would like to show a running total of the payments already done to that specific invoice, so that I can calculate the remaining balance.
I tried the following approaches, with no success:
1. I tried with a query that will total payments by invoice (qry_total_payment), but if I include the query in the subform query, then I can't edit the fields in the form.
2. I created a field in the Invoice table, where I would store the sum of the payments from the other table. But I don't know how to update the field using qry_total_payment query. The update query in access does not let me use a "totals" query. Any ideas?

P.S. I know option 2 is not a good practice, but don't see another way around?

 

[DavidAtWork]

If all else fails you can use a 2 step approach using your option 2 above. Create the sum query and make it a 'Make Table' query, run it and then use the resultant table to update the sum of payments field in your Invoice table

David

 

[LPurvis]

Though it's not a particularly rigorous or efficient suggestion, it does circumvent the (as you observed) undesirable practice of storing a calculated value in your invoice table...
You could just use a DLookup into a field to allow display of the sum value from a query while maintaining the updatability of your form otherwise.

I would ask though... You say:
>> but if I include the query in the subform query
and yet you also say
>> I have a sub-form(datasheet view) used to input the parts of the payments by Invoice

Why are you attempting to include the paiddtotal in the subform?
Do you actually want a running total - based on date of entry with each payment part? (You're always going to hit noticable overhead in this. But your lookup could still work.)
If it's just to know how much has been paid in total this far, then that would surely be part of the main invoice form, and not in the subform?

Cheers

 

[fegarci]

Thank you to all, I used DavidatWork sugestion, and it worked.

LPurvis, I have to show the running total in the subform because the payment can apply to more than one invoice, so the running total is of the portion of the invoice paid.

Thanks.

 

[LPurvis]

I'm afraid I'm not at all clear on what you mean by that - but you seem to be OK running with it, so that's good.