Results 1 to 2 of 2
  1. #1
    CaptOlson is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    1

    Question Linked Tables - Appending Records into Both Tables

    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!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some insight from this free data model at Barry Williams' site.

    It outlines the tables required and their relationships to one another in a typical training environment. This is a generic model and may not have all details you may need, or may have more detail than you need. It is meant as a starting point to help you with your tables and relationships.

    In many organizations there are People and Positions. Positions often require a specific level of expertise. A Person intent on occupying a Position may or may not have the required expertise and is required to take training as a condition of being acceptable to a Position.

    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  2. Replies: 5
    Last Post: 10-22-2013, 07:37 AM
  3. Replies: 3
    Last Post: 05-13-2013, 10:16 PM
  4. Replies: 2
    Last Post: 11-05-2010, 04:47 AM
  5. Appending Records to tables with relationships
    By RubberStamp in forum Import/Export Data
    Replies: 0
    Last Post: 12-14-2008, 06:52 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums