Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to link tables

    I want to create a form where a user can type in an event, and date, then from a list of staff, select who is available to work. This needs to be saved, so a report can be printed. Also, the user needs to be able to able to type in another event for the same date, and select more staff, with a possibility of selecting the same staff, as they could work one event in the morning, and another in the afternoon. I just don't know how to link the tables up, when all I would have on the main form is the Event and date. The employee table has Employee ID as autonumber. Any suggestions.

  2. #2
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    You will need a third table. The will be a junction table to link the Employee to the event.

    Example:

    tblEcentEmployees
    EventEmployeesKey - autonumber - primary key
    EE_EventID - long- foreign key to link to the Events table
    EE_EmployeeID - long - foreign key to link to the Employee table
    EE_position - long - foreign key to link to the positions look up table
    EE_Notes - text 255 or memo

    If an employee can only be assigned to a single event doing a single position then I would also create a unique index that uses both the EE_EventID and EE_EmployeeID fields.

    If an employee can be assigned to to multiple positions for single event and also to multiple positions then I would also create a unique index that uses the three fields EE_EventID, EE_EmployeeID, and EE_position.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks. Done it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •