Adding Records with an Update Query

[Microsoft]

I am putting together a database of volunteers for a non-profit as a way of
learning Access, and it is almost done. One of the last modules I am doing
allows data entry volunteers to put records in an Excel spreadsheet with the
same fields (many don't have Access). My module will import the new data
into separate Access database with the same structure as the main one and
then put new records into Access after checking for duplicates. Several
fields will be in the duplicates logic (last name, first name, address and
phone number, not all ands).

I'm sure I'm overlooking something very obvious, as I have searched the main
Access newsgroup and others, and the answer always is use an update query.
I have tried that and can't figure out how the query would work. I could do
this with VBA, and I will, if I can't figure it out soon, but the idea was
to learn Accesss. Can anyone help me out?

TIA,

John

John Cunningham
Silver Spring, MD
(e-mail address removed)

 

[spencer4]

Update queries don't Add records, they change the data in the fields in an
existing record.

Append queries ADD new records.

So, which are you trying to do - modify existing records (Update Query) or add
new records (Append Query)?

 

[Microsoft]

Thanks for the distinction. I want to append records that meet the criteria
for uniqueness.

Thanks, again,

John

John Cunningham
Silver Spring, MD
(e-mail address removed)

 

[Dale Fye]

John,

Format would look something like:

INSERT into Table1 (field1, field2, field3)
SELECT T2.Field1, T2.Field2, T2.Field3
FROM Table2 T2
LEFT JOIN Table1 T1
ON T2.PKField = T1.PKField
WHERE T1.PKField IS NULL

HTH
Dale

BTW, You can create what some call an UPSERT (Update & Insert) query in
Access (but not in SQL Server). Format is something like the following.
This uses a table I have been playing with that has MachineNo and JobNo as
the primary key. This is handy for cases when you are using distributed,
disconnected databases, which get passed back to a central office on a
recuring basis. If one of the sites made an entry, sent the database in,
then changes something in that record, this will update the main table,
while also inserting any new records.

UPDATE Table2 AS T2
LEFT JOIN tbl_MachineTimes AS T1
ON (T2.JobNo = T1.JobNo)
AND (T2.MachineNo = T1.MachineNo)
SET T1.MachineNo = [T2].[MachineNo]
, T1.JobNo = [T2].[JobNo]
, T1.Duration = [T2].[Duration];

 

[Microsoft]

Thanks, Dale

John