group update method

[Guest]

have query results - lets say all employees with hire date in December month.

I want to document that each went to training session XYZ.

There will be the recurring need to do updates for future training down the
road to groups of employees.

How to do it without having to open each individual employee record and
posting? (A highly manual/repetitive task....) The freedom to create any
new Training tables is ok.

First thought: Make XYZ as a single field, single record table and then join
to the query group?...with a MakeTable or AppendTable query.

thoughts on other approach is welcomed...thnx

 

[Guest]

Hi NTC,

It depends how you are currently documenting that employees went to a
training session. Let's say that you are recording the Training Session and
Date in a table.

Use an insert query to bulk insert the records, similar to this:

docmd.RunSQL "insert into tblEmployeeTrainingSession (EmployeeID,
TrainingSessionID, TrainingSessionDate) select EmployeeID, 12 as
TrainingSessionID, #2006/12/13# as TrainingSessionDate from tblEmployees
where EmployeeStartDate between #2006/12/01# and #2006/12/31#"

Of course, you could pick up the date/sessionid from some form.

Hope this helps.

Damian.

 

[Guest]

ok thanks for input. will experiment with approach also.

employee table is established but have free hand to create training history
table however I want.

if they crank 500 employees thru a course - no one wants to manually update
individual records....have query that will collect the correct 500 into a
set...

will play around with it...thanks for reply...

 

[pietlinden]

have query results - lets say all employees with hire date in December month.

I want to document that each went to training session XYZ.

There will be the recurring need to do updates for future training down the
road to groups of employees.

How to do it without having to open each individual employee record and
posting? (A highly manual/repetitive task....) The freedom to create any
new Training tables is ok.

First thought: Make XYZ as a single field, single record table and then join
to the query group?...with a MakeTable or AppendTable query.

thoughts on other approach is welcomed...thnx

I would create a deliberate cartesian product.
SELECT tblCourses.CourseID, tblEmps.EmployeeID
FROM tblCourses, tblEmps
WHERE CourseID='X' <===select specific course
AND tblEmps.HireDate>=#12/01/06# and tblEmps.HireDate<#1/1/07#
<===select new hires

then turn that into an append query. The cartesian product multiplies
the two sets... then you just append.