Global entry of records

[Tim]

I have a training database where I keep track of training for employees. If
I want to update 10 of those records with the same information, how would I
go about doing this instead of updating each individual record?

I have tried tagging those records that needed to be updated and used an
append query but that didn't work.

 

[Tom Wickerath]

Hi Tim,

You need to use an Update query with the appropriate criteria, not an Append
(adds new records) query. Start with a normal SELECT query to select the
records of interest, using the appropriate WHERE clause (criteria). Then
convert it to an Update query.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Tim]

That will not work in my database. I know how to update records using a
query but I want to add a new record for each employee who took a training as
a global procedure so I will not have to go to each employee and add the new
training.

 

[John Spencer]

Do you have a table of employees? Can you identify (in a query) the employees
you want to add a new training record to? What is the structure of your tables?

Generically, you might have a query like the following.

INSERT INTO EmployeeTrainingSessions (EmployeeID, TrainingID, TrainingDate)
SELECT EmployeeID, 1233, Date()
FROM Employees
WHERE EmployeeName like "S*"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Tom Wickerath]

Hi Tim,

Given that your original statement included "If I want to update 10 of those
records...", an update query is the solution. Now, if you really meant that
you need to *add* 10 new records, then an append query is, indeed, the
correct path. But don't use the word "update" if you really mean "add", as
that just adds confusion.

Perhaps you can post the SQL for the append query that you used, which you
stated "but that didn't work". Tell us exactly what "didn't work" means. Did
you receive any error messages? Did any records get added? If you preview the
results in query design view, were the proper records selected?

Perhaps all you need is an intentional cartesion product result. A cartesian
product represents all of the rows selected from one table multiplied by all
of the rows selected from another table. So, try adding the Employees table
and the Courses table to a new query. Add the appropriate criteria such that
only one course is selected, and only the employees who took this course are
selected. Your query should not include any joins between these two tables.
Now, if you select 1 record from the Courses table, and 10 records from the
Employees table, the cartesian product recordset should include 1 x 10 = 10
records. You'll want to include primary keys from each table, appending these
values to the corresponding foreign key fields in the intersection table that
normally serves to join these two tables (but, which you intentionally omit
from the query design view). Does this help?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________