Updating records through/around a Totals Query

[Fresco]

On large invoices, it's better cash flow at times to pay only part now, part later. I tried using a totals query to build a recordset that show a listing of invoices that have balances owing with the original balance and the sum of any and all payments that have been made.

I am using this query to populate a form which would allow me to select which invoices I want to pay with this check (setting the control "paynow" to "yes") and how much I want to pay on each (setting the unbound control "payamt" to an amount less than the balance if I choose).

I got this far, only to realize that the form was not updatable. So, I went back to the Totals Query and found it was not updateable.

I'm using the tables and controls as follows for the query:

VenTbl
VenID
Vendor GroupBy Like [Select Vendor]

InvoiceTbl
InvID
Inv# GroupBy
InvDate GroupBy
InvAmt GroupBy
VenID GroupBy Lookup to VenTbl
PayNow GroupBy

PayDetailTbl
DetID
InvID Lookup to InvoiceTbl
PdAmtApplied Sum

The relation between InvoiceTbl and PayDetailTbl includes all records from InvoiceTbl and only those from PayDetailTbl where the joined fields are equal.

Ideas on how to make the record set updatable? It's probably something obvious, but after several hours of attempts and reading 'help', I'm stuck.

Thanks in advance,
Fresco

[Fresco]

Fresco: more thoughts:

I've been thinking through the question I asked and it's non-sensical. A totals query combines the many side to total one or more of it's fields. Access wouldn't know which record in the many side to update.

So, I'll try something else. I'll use the totals query as the basis for a continuous form and add a command button which when clicked will open a second form with subform for just that one invoice with invoice #, vendor name & invoice date in the main form and any/all payments with check #s and dates in the subform.

I'll place a control on the main form showing how much I want to pay out of the remaining balance and with a command button, launch the Check report for printing the check.

Anyway, if you read these two posts and have a better idea/approach, let me know.

Fresco

[MMcCarthy]

Its a little confusing but I think I know what you are getting at. You could look at changing your structure slightly. The following structure is just an example.

tblInvoices
InvoiceNum (Primary Key)
InvoiceDate
InvoiceAmount
VendorID (FK reference to tblVendors)

tblChecks
CheckNum (Primary Key)
CheckDate
CheckAmount
VendorID (FK reference to tblVendors)

tblPayments
PaymentID (Primary Key)
CheckID (FK reference to tblChecks)
InvoiceNum (FK reference to tblInvoices)
PaymentAmount

This way your payments will record a reference to both the invoice and the check. You won't need to update anything as you will add a new record to tblPayments to show each part payment covered by the check and which invoice it is paid against.