Question

Locked

MS Access Nested Update Query

By martinbullock15 ·
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

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Hmm theres some T_SQL access's parser just won't have

by Tony Hopkinson In reply to MS Access Nested Update Q ...

I don't use access that much, and try not to at all, but my first thought would be to put the inner query in an intermediate query and then substitute in yours.

Collapse -

Hmm theres some T_SQL access's parser just won't have

by Tony Hopkinson In reply to MS Access Nested Update Q ...

I don't use access that much, and try not to at all, but my first thought would be to put the inner query in an intermediate query and then substitute in yours.

Your description doesn't match what you are doing either, tblTotals is a table isn't it, not a query...

Collapse -

Yea

by martinbullock15 In reply to Hmm theres some T_SQL acc ...

Yea tblTotals is a table i am updating, so tblTotals only has one occurence of each barcode where the total of the quantities from tblStockOut will be put e.g.

tblStockOut

Barcode --- Quantity
10001 -------- 1
10002 -------- 2
10003 -------- 4
10002 -------- 2
10001 -------- 5
10001 -------- 4


tblTotals

Barcode --- Quantity
10001 -------- 10
10002 -------- 4
10003 -------- 4

I know i don't usually use Access table but i have to as this is what this part of the system is built on.

Thanks

Collapse -

Presumably tbltotals wouldn't be changed by a user

by Tony Hopkinson In reply to Yea

So unless it's really huge you could just make it a group by query and show the answer when required.
Or if that is going to break downstream stuff, empty it out and repopulate, given it's not happening a lot.
Update .. = (select) is hideously slow anyway for more than one record, inner query get's run in full every time.

Might be better to do one sum query, and then use that to do the update .. (select ...) as it will only be doing a find, DBMS might build a temporary index to speed it up as well.

Back to Networks Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums