Updating TABLE with Query ( Update?)

[Guest]

I have a table that i would like to update with results of a seperate query

I would like to update

table1.Title with query.title
table1.quantity with query.quantity

where table1.item = query.item

 

[Guest]

I redid a search on what iwas looking for and found the following update
query

UPDATE ELEM_SCHEM INNER JOIN [LOCATOR FIlter 131] ON ELEM_SCHEM.ITEM =
[LOCATOR FIlter 131].ITEM SET ELEM_SCHEM.TITLE = [LOCATOR FIlter 131]![TITLE];

I get the following error Operatio must use an updateable query

 

[Guest]

So I had to use maketble query and then update from the temp file to get it
to work.

Is that a work around or the way it needs to be done ?

 

[John Spencer]

It may be the workaround that you need. It is hared to say since we don't
know what type of query Locator Filter 131 is and if it is updatable. If
you run the query Locator Filter 131 by itself can you update records or add
records. If not then you can't use it in an update query. You might be
able to use the DLookup function or one of the other VBA aggregate
functions.

Assuming Item is a text field then you should be able to use the following
to update the Title feld.
UPDATE ELEM_SCHEM
SET ELEM_SCHEM.TITLE =
DLookup("Title","[LOCATOR FIlter 131]", "Item = """ & [Item] & """")

If there are a large number of records to update then this may be too slow.
In that case, you may have to use the temp table method to get satisfactory
performance.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..