Update query based on value in a different record of the same table

[Dale Peart]

Is it possible to update a field of one record (rec2) based on the value of
the same field in a different record (rec1) where rec1 is the source of rec2
and both are in the same table? Or do I have to create an intermediate
table and then link the 2 tables and do an update query?
Example
Table1
ID Site Date Source
111 Site52 1/1/01
222 111

I want to update the 222 record to include the Site and Date info based on
the 111 record data but can't figure how to do the links.

 

[John Spencer (MVP)]

You can do this by adding your table into the query twice and joining one
table's ID to the other table's source. SQL statement would look something like

UPDATE Table1 as A INNER JOIN Table1 As B

 

[John Vinson]

Is it possible to update a field of one record (rec2) based on the value of
the same field in a different record (rec1) where rec1 is the source of rec2
and both are in the same table? Or do I have to create an intermediate
table and then link the 2 tables and do an update query?
Example
Table1
ID Site Date Source
111 Site52 1/1/01
222 111

I want to update the 222 record to include the Site and Date info based on
the 111 record data but can't figure how to do the links.

Create a Self Join query by adding Table1 to the query grid *twice*.
Join ID of the first instance to Source of the second instance, and
make it an Update query; update table.Site to

=[Table_1].[Site]

and similarly for the date field.