Hi
I have been struggling to convert this query that i have used for ages in T-SQL to MS Access, it works in SQL server but i get an error when i try to use it in MS Access, the error says “Operation must use an updatable query”
The update is to get the total amount of sales from the sales table for each Barcode for the last x number of days, and then put the total for each barcode against that barcode in a different table where each barcode only appears the once.
This is the query straight from SQL:
UPDATE tblTotal SET Sales = (SELECT SUM(tblStockOut.Quantity)
FROM tblStockOut
WHERE tblTotal.ProductBarcode = tblStockOut.ProductBarcode
AND SalesStream_tblStockOut1.BranchID = ‘A1’
AND ControlDate > dateadd(DAY,-90,getdate()))
WHERE tblTotal.BranchID = ‘AB’
This is the Query i am trying to use in Access when i get the error:
UPDATE tblTotal SET Sales = (SELECT SUM(tblStockOut.Quantity)FROM tblStockOut, tblSuggested
WHERE tblStockOut.ProductBarcode =tblTotal.ProductBarcode
AND ControlDate > dateadd(‘D’,-90,date()))
WHERE tblTotal.BranchID = ‘AB’
Thanks in advance for any help
Kind Regards
