copy all values to different existing record

[Guest]

I need a query to take all the values from one record (except the Key field)
and put those values into corresponding fields of a second record in the same
table.

What is the cleanest way to do this?

 

[John Spencer]

An append query would be the way to go. What type of field is your
primarykey? Is it an autonumber?

INSERT INTO YourTable ([KeyField], FieldA, FieldB, FieldC,...)
SELECT "ABC124", FieldA, FieldB, FieldC,..
FROM YourTable
WHERE KeyField= "ABC123"

If the keyfield is an autonumber then leave it out of the field lists. If
the PK is some other value then you will have to supply it by some means.

 

[Van T. Dinh]

Try something like:

UPDATE Table1 As Dest, Table1 As Src
SET Dest.[FieldX] = Src.[FieldX],
Dest.[FieldY] = Src.[FieldY] ...
WHERE Dest.[KeyField] = 123
AND Src.[KeyField] = 456