I have inherited a database at work, and it contains training records for our employees. In that database, my predecessor recorded each employee's first day of training as "Orientation". I want to change that to actually list ALL the training topics they covered (which is around 40).
Here's what I have:
Table: tblTrainingEvents
Fields: TrainingEventID (AutoNumber)-[Primary Key], TrainingDate, TrainingTopic
This table is linked One-To-Many to the next table...
Table: tblTrainingRoster
Fields: TrainingEventID, Trainee
So, right now I have for example:
tblTrainingEvents: 40711921, 23-Nov-08, Orientation
tblTrainingRoster: 40711921, John Doe
What I want to do remove the original "40711921" record and replace it with a list of topics on the same date (but with new Autonumbers, of course) and also add new records to TrainingRoster to show that John Doe attended each of those new training events.
Here's an example:
What I have:
tblTRAINING EVENTS
40711921 23-Nov-08 Orientation
tblTRAININGROSTER
40711921 JOHN DOE
What I want:
tblTRAINING EVENTS
40711921 23-Nov-08 Anti-Discrimination Policy
40711922 23-Nov-08 Confined Space Entry
40711923 23-Nov-08 Drug & Alcohol Policy
40711924 23-Nov-08 Environmental Policy
tblTRAININGROSTER
40711921 JOHN DOE
40711922 JOHN DOE
40711923 JOHN DOE
40711924 JOHN DOE
Except that there will be far more training topics than just four. Around 40-50 per employee, and there are almost 2,000 employees. Manually updating this is not an option unless we hire a dedicated data entry clerk!
(One last note: in the example I gave, John Doe was alone on orientation day. But there may be several Trainees in tblTrainingRoster for each record in tblTrainingEvents.)
Any help would be greatly appreciated!