Update Query based on calc. Field in another query

C

Cmenkedi

I have a table I want to update based on the calculation in another query.
I have tblFutureOrder with the field Archive and
qryFutureOrderUpdate with the calculated field
AmountLeft:[OrderedAmount]-[ShippedAmount].
I want tblFutureOrder.Archive to equal Yes when Amount < .
So far I have not been able to come up with an answer. I keep getting the
error "Operation must us an updateable query."
Here is my sql:
UPDATE tblFutureOrder LEFT JOIN qryFutureOrderUpdate ON
tblFutureOrder.FutureOrderId = qryFutureOrderUpdate.FutureOrderId SET
tblFutureOrder.Archived = -1
WHERE (((tblFutureOrder.Archived)=0) AND
((qryFutureOrderUpdate.AmountLeft)>1));

Thank You
 
J

John Spencer

I am going to guess the the problem is with qryFutureOrderUpdate. Is it a
totals (aggregate) query or does it have other characteristics that will
trigger the message?

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

You might be able to use the following:

UPDATE tblFutureOrder
SET tblFutureOrder.Archived = -1
WHERE tblFutureOrder.Archived=0 AND
FutureOrderID IN
(SELECT FutureOrderID
FROM qryFutureOrderUpdate
WHERE AmountLeft>1)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
C

Cmenkedi

Thank you that was what I was looking for.


John Spencer said:
I am going to guess the the problem is with qryFutureOrderUpdate. Is it a
totals (aggregate) query or does it have other characteristics that will
trigger the message?

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

You might be able to use the following:

UPDATE tblFutureOrder
SET tblFutureOrder.Archived = -1
WHERE tblFutureOrder.Archived=0 AND
FutureOrderID IN
(SELECT FutureOrderID
FROM qryFutureOrderUpdate
WHERE AmountLeft>1)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a table I want to update based on the calculation in another query.
I have tblFutureOrder with the field Archive and
qryFutureOrderUpdate with the calculated field
AmountLeft:[OrderedAmount]-[ShippedAmount].
I want tblFutureOrder.Archive to equal Yes when Amount < .
So far I have not been able to come up with an answer. I keep getting the
error "Operation must us an updateable query."
Here is my sql:
UPDATE tblFutureOrder LEFT JOIN qryFutureOrderUpdate ON
tblFutureOrder.FutureOrderId = qryFutureOrderUpdate.FutureOrderId SET
tblFutureOrder.Archived = -1
WHERE (((tblFutureOrder.Archived)=0) AND
((qryFutureOrderUpdate.AmountLeft)>1));

Thank You
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query - Operation must use an updateable query 3
Update query on a query on the latest date 1
Update Query Error 5
can't execute update query 1
Update query. Within the same table: populating field 2 from field 6
Query Based on Another Query 7
update query based on two fields 4
Crosstab query as data source for update query 7

Top