Update & Append in one query?

[Bradley C. Hammerstrom]

Access2000

Is there a way to do two things at once?

I want to update TableOld using TableNew to include revisions made to
existing records, and also to add any new records.

I know I can write an update query and an append query to do this
separately; can these be combined somehow?

Brad H.

[Dale Fye]

Bradley,

In Access, yes, sometimes referred to as an UPSERT query. In SQL
Server, no.

Link TableNew to TableOld using a left join (include all from new)
base on whatever your key field is.
Change the query type to an UPDATE query,
Then drag every column in TableOld to the Grid and in the update row,
reference that column from TableNew.

The SQL might look something like the following if you were updating
the lastname, firstname,and SSN values. The down side of this
particular example is that I used the SSN as the key field, and if you
changed the SSN in the new table, you would end up with a new record
(same person but with two different SSNs), so you need to make sure
that the key field you are using to link the two tables has not been
changed.

UPDATE tblNew
LEFT JOIN tblOld
ON tblNew.SSN = tblOld.SSN
SET tblOld.NameLast = [tblNew].[NameLast],
tblOld.NameFirst = [tblnew].[namefirst],
tblOld.SSN = [tblnew].[ssn];

--
HTH

Dale Fye

"Bradley C. Hammerstrom" <bhamme...@NOSPAMrhadcock.com> wrote in
message news:#OeW07vc...@TK2MSFTNGP09.phx.gbl...