Update using Select where fields match

[j.t.w]

Hi,

This should be pretty simple but, I just can't figure it out.

Below are some queries that I've tried but don't work. I'm wanting to
update the PackAmt field in TblA with the values in the Pack_Amt field
in TblB where the invoice numbers match. Notice below the CSTR and the
aggregate in the below queries.

UPDATE TblA
SET (TblA.PackAmt) = (SELECT SUM(TblB.Pack_Amount)
FROM TblB
Group by TblB.Invoice_No)
Where CSTR(TblA.InvoiceNo) = TblB.Invoice_No


Update TblA
Set (TblA.PackAmt) = (SELECT SUM(TblB.Pack_Amount) As PackAmt
FROM TblA INNER JOIN TblB ON CSTR(TblA.InvoiceNo) = TblB.Invoice_No
GROUP BY TblA.InvoiceNo)
FROM TblA, TblB as t2
Where TblA.InvoiceNo = TblB.InvoiceNo


UPDATE TblA Inner Join TblB
ON CSTR(TblA.InvoiceNo) = TblB.Invoice_No
SET CommissionSummary.PackAmt = (SELECT SUM(TblB.Pack_Amount) As
PackAmt
FROM TblA INNER JOIN TblB ON CSTR(TblA.InvoiceNo) = TblB.Invoice_No
GROUP BY TblA.InvoiceNo)

Please let me know how you think I can get this to work.

Thanks,
j.t.w

 

[John Spencer]

Unfortunately you can't use aggregate data in an update query. Plus it
is usually not a good idea to store aggregated data if the underlying
data will change. If your database is for analysis then it often does
make sense to do so.

One solution is the use the DSum Function

UPDATE TblA
SET (TblA.PackAmt) =
DSUM("Pack_Amount","tblB","Invoice_No=""" & [InvoiceNo] & """")



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================