create new records from udate query

[HeatherBee]

I have a database with several peripheral tables that
relate back to a main 'Volunteers' table. I would like
to update one these peripheral tables based on criteria
from the 'Volunteers' table. Rather than update existing
records based, however, I need new records to be created
in the peripheral table based on the Volunteers table
(and excluding any VolID's who already have a record of
that name).

Currently, I can only get the query to update records
already in the Training Table, not add new ones. I am a
relatively new user, so I am doing this through
the 'design view' rather than 'SQL'. That said,
the 'SQL' generated through my efforts is:

UPDATE Volunteers LEFT JOIN VolTraining ON
Volunteers.VolID = VolTraining.VolID SET
VolTraining.Description = "Volunteer Reception",
VolTraining.InvitationSent = Yes
WHERE (((VolTraining.Description)<>"Orientation" And
(VolTraining.Description)<>"Volunteer Reception") AND
((Volunteers.RegistrationReceived)=Yes));


Any advice that anyone could give would be greatly
appreciated!

Heather

 

[Ted Allen]

Hi Heather,

An update query is only for changing existing records as
you mentioned. To add new records, change the query to
an append query. When you create the append query, it
will prompt you for the table name that you want to
append the records to. Then, choose the data source for
your new records (field values from existing tables
and/or expressions that generate values), and set any
necessary criteria. Finally, near the criteria line
there will be a line to specify which field to append
each of your data source fields to in the destination
table.

This is fairly general info, if you try it and have any
problems post back and I'll give more detailed
instructions.

-Ted Allen

 

[John Vinson]

Currently, I can only get the query to update records
already in the Training Table, not add new ones. I am a
relatively new user, so I am doing this through
the 'design view' rather than 'SQL'. That said,
the 'SQL' generated through my efforts is:

UPDATE Volunteers

An Update query is designed to update existing records. I think what
you want instead is an Append query, which is designed to append new
records.