Update Query based on calc. Field in another query

  • Thread starter Thread starter Cmenkedi
  • Start date Start date
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
 
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
 
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...
 
Back
Top