Update a query to value in another table

[Tim]

I need to run an update query that takes its value from
another a table. This is all part of a series of SQL
statements I need to run to bulk load some records into a
linked up table. What happens is:
1) the max id number is selected into a temp table(a)
2) Update query adds 1 to that number
3) On anohter temp table(b), records are added via other
SQL statements
4) I need to update the column of my second temp table(b)
with the 1 recrd (under 1 column) in tempo table(b).
5) I then update the linked table with the contents of
temp table(b), and everythings great.

A few other things happen after that, but its this one
bit I'm stuck on, and (as per a previous post) I just
get 'must use updateable query" errors.

Tia
Tim

 

[Steve Schapel]

Tim,

I assume you have made a typo in point 4 and it should say table(a)
instead of table(b)?

By the way, as a side issue, it is not a good idea to use characters
like ( and ) as part of the name of database objects.

So, if I understand you correctly, you could use a DLookup function in
your update query, so the SQL view of the query will look something like
this...
UPDATE table(b) SET [the column] = DLookup("[1 column]","table(a)")