Update text field field

[darees1]

I have created a query which compares the utility meter readings to a
previous average. The query identifies where variances are in excess
of 30%. The problem I have is that I am attempting to use an update
query to insert the text 'excessive variance' in a comments field
against the original meter reading, but get the error 'operation must
use an updatable query'. Other posting that have solve this problem
with a DLOOKUP but I cannot see how I would this as I only want to
insert text.

The SQL code I have written is: UPDATE Readings INNER JOIN [Recent
Meter reads] ON Readings.Meter_ID = [Recent Meter reads].Meter_ID SET
Readings.Comments = "Excessive Variance";

Any help would be greatly appreciated.

 

[Allen Browne]

Try a subquery in the WHERE clause.
Something like this:

UPDATE Readings
SET Readings.Comments = "Excessive Variance"
WHERE EXISTS
(SELECT Meter_ID
FROM [Recent Meter reads]
WHERE [Recent Meter reads].Meter_ID = Readings.Meter_ID);

You may already be aware of subqueries. There's an example here of how to
use one to get the previous meter reading:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[John Spencer]

Try the following
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.

UPDATE Readings
SET Readings.Comments = "Excessive Variance"
WHERE Readings.Meter_ID IN
(SELECT Meter_ID FROM [Recent Meter Reads] )



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