Update Query question

[Steve]

I have a query to give me a balance for invoices. This works OK. This is the
SQL:
SELECT DISTINCT tblInvoices.InvoiceId, tblInvoices.InvoiceDate,
tblInvoices.InvoiceLeaseAmount, tblInvoices.InvoiceElectric,
tblInvoices.InvoiceWater,
[InvoiceLeaseAmount]+[InvoiceElectric]+[InvoiceWater] AS InvoiceTotal,
qryPaymentTotals.TotalPayments, [InvoiceTotal]+[TotalPayments] AS Balance,
tblInvoices.InvoicePaid
FROM tblInvoices INNER JOIN qryPaymentTotals ON tblInvoices.InvoiceId =
qryPaymentTotals.InvoiceId;

The qryPaymentTotals SQL is:

SELECT tblPayments.InvoiceId, Sum(tblPayments.PaymentAmount) AS TotalPayments
FROM tblPayments
GROUP BY tblPayments.InvoiceId;

What I would like to do is update a field whenever the balance is zero.
When I give it my best shot I get the following error:

"Operation must use an updateable query"

InvoiceId is the PK for the invoice table and FK for the payment table.

TIA for your help.

Steve

 

[Duane Hookom]

A query containing a total/group by is never updateable in Access. If you
really have a need to update a field that can be calculated on-the-fly, you
could use DSum() in place of qryPaymentsTotals.

 

[Steve]

Thanks for the quick reply. Really helped get me on the right track!

A query containing a total/group by is never updateable in Access. If you
really have a need to update a field that can be calculated on-the-fly, you
could use DSum() in place of qryPaymentsTotals.

--
Duane Hookom
Microsoft Access MVP

I have a query to give me a balance for invoices. This works OK. This is the
SQL:
SELECT DISTINCT tblInvoices.InvoiceId, tblInvoices.InvoiceDate,
tblInvoices.InvoiceLeaseAmount, tblInvoices.InvoiceElectric,
tblInvoices.InvoiceWater,
[InvoiceLeaseAmount]+[InvoiceElectric]+[InvoiceWater] AS InvoiceTotal,
qryPaymentTotals.TotalPayments, [InvoiceTotal]+[TotalPayments] AS Balance,
tblInvoices.InvoicePaid
FROM tblInvoices INNER JOIN qryPaymentTotals ON tblInvoices.InvoiceId =
qryPaymentTotals.InvoiceId;

The qryPaymentTotals SQL is:

SELECT tblPayments.InvoiceId, Sum(tblPayments.PaymentAmount) AS TotalPayments
FROM tblPayments
GROUP BY tblPayments.InvoiceId;

What I would like to do is update a field whenever the balance is zero.
When I give it my best shot I get the following error:

"Operation must use an updateable query"

InvoiceId is the PK for the invoice table and FK for the payment table.

TIA for your help.

Steve