Update table with a relationship to a totals query

[TimTDP]

I have the following tables:
tblInvoice
tblInvoicePrePayments
They are related with a one-to-many relationship. The related field is "InvoiceId"

I want to create a query that shows selected fields from tblInvoice and the sum of field "PrepaymentValue" from table tblInvoicePrePayments.

I want to be able to edit the fields from tblInvoice in the query.

If I create a totals query (qryInvoicePrePaymentsSum) on tblInvoicePrePayments, using the fields InvoiceId and PrepaymentValue, and then create another query with tblInvoice & qryInvoicePrePaymentsSum, the fields in tblInvoice cannot be updated!

What is the most efficient method I should adopt?

Thanks in advance

 

[namliam]

there is no method that will work while you combine the two... Your tblInvoice joined with any group by query will always cause it to become unreadable....

I seriously doubt your need to edit the table, since it (IMHO) should simply be reliant on current invoice - prepayed amount, or something like that.

If you want to persue this, I would probably display the information in a form with 2 seperate subforms... One form showing the Invoice information, the other the sum... both of the same invoice as comming from the main form.

 

[TimTDP]

Ok thanks
The other option is to use a Dlookup or Dsum
Would it be more efficient to:
1. Create the totals query and then use Dlookup to display the total or
2. Use Dsum to display the total

Thanks

 

[namliam]

I would say DSum, using either in a query is VERY resource demanding though.