Update a table using Query

[jdcallejas]

Ok,
I have a fields called TotalAmount in my Invoice table, I have my subform with my details. Since I cant seem to get my TotalAmount field to get the sum of all my details, I will need to run a query that does that for me.

So I create a query that sums my invoice details and save as SumofDetails. than I create an update query that has my table invoices and the query SumOfDetails. I tell it to update my TotalAmount with my SumOfDetails Amount.

I get this error:

Operation must use an updatable query.


Any ideas how i can do this?

Daniboy

 

[MrQ]

Can you post the SQL for your query?

 

[jdcallejas]

hello

MrQ , here are both queries,

Sums my TotalDetails;
SELECT Invoices.InvID, Sum(InvoiceDetail.InvDetailAmount) AS SumOfInvDetailAmount
FROM Invoices INNER JOIN InvoiceDetail ON Invoices.InvID = InvoiceDetail.InvID
GROUP BY Invoices.InvID;

Update TotalAmount on Invoice table with the totaldetail value on the query above;

UPDATE Invoices INNER JOIN UpdateInvAmount1 ON Invoices.InvID = UpdateInvAmount1.InvID SET Invoices.InvAmount = [UpdateInvAmount1]![SumOfInvDetailAmount];

When I run this I get the error I said above.

Hello Pat,
I already have an unbound field un my subform footer that totals my details;
=Sum([InvdetailAmount])

Than I have another unbound field on my form;
=Format([InvoiceDetail subform].[Form]![DetailTotal],"Currency")

This is how I am displaying my Grand Total on my from. The problem is that I cant get that value to save on my bound field from my table invoice.

Thats why I thought I put an update query to run every time the form was closed to update this field but I get that error I talked about.

Thanks,

Daniel

 

[LennyNero]

hi,

I used this on a piece of code, it worked very nice.
I have a problem though, although i have a number with decimals, like 1500,29858 it displays on the main form as 1500,00

What am i doing wrong?

Thanks
Luis