Results 1 to 7 of 7
  1. #1
    Stewww is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    1

    Many to Many Relationship Question

    Hi,

    If i was creating a many to many relationship between an Employee table and a Training Course table can i ensure that each course is accounted for when a new employee is added and if a new course is added it will show as an incomplete course for that employee (therefore automatically populating the employee userform with a new field)

    My goal was an employee form that would have a sub form which would include the entire current list of courses (lets say 15 in this case) and a date field for when it is carried out - but i assume the creation of that list would need carried out via sql once a new employee is added (ie employee 8 added > autocreate 15 records in junction table, one for each course). The reverse would be once a new course is added > auto add 8 records to junction, 1 for each employee

    I havent dealt with any many to many relationships before so theres a good chance im overlooking a much more simple solution!

    Any help appreciated

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    See this info

    and use google to find additional examples/articles.

    There are free videos by Dr. Daniel Soper that may be of benefit to you. Watch #1,2 and 4.
    They start here.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This isn't really a many to many relationship

    This is 3 tables

    Employees
    Classes
    EmployeeClasses

    Where employee classes is a junction table
    The relationships would be
    one (employee) to many (employee classes)
    one (class) to many (employee classes)

    Your junction table would hold the cartesian possibilities of employee/class (all employees and all classes) along with any other employee/class specific information (like your date of completion)

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I took the question at face value --dealing with Many to Many relationship.

    If a database designer is faced with two tables, A and B, that are related via a many to many relationship, the designer would likely introduce a third table, say C, where A and C will be related via a one to many relationship and similarly, B and C will be related via a one to many relationship.

    Many to many relationships can be decomposed into two one to many relationships.
    Here is a model that may be useful to you, especially if there is training related to positions and training related to people who may apply/occupy those positions.

    Click image for larger version. 

Name:	EmployeePositionTraining.jpg 
Views:	11 
Size:	40.9 KB 
ID:	25809

    Good luck.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I think he wants to show all the classes available when you select an employee, even the ones they have not taken yet. Maybe you can do it with a left join somewhere? But I think as you stated you would have to write some VBA to add the entries to the EmployeeClasses table when a new Employee or a new Class is added and just leave the DateCompleted blank.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    no, he doesn't have to write vba. If he uses a junction table it's very easy to create a cartesian append query and run it whenever an employee or class is added.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bulzie,

    I think you are correct -- we're still guessing at what the Op wants/needs. I can see lots of examples where
    Not all courses applied to every Employee.

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

Similar Threads

  1. Relationship question
    By mjwakema in forum Database Design
    Replies: 4
    Last Post: 10-28-2015, 05:13 PM
  2. One to One Relationship question
    By ittechguy in forum Database Design
    Replies: 2
    Last Post: 09-09-2015, 07:50 AM
  3. Relationship Question
    By Collins in forum Database Design
    Replies: 6
    Last Post: 06-04-2015, 12:07 PM
  4. Relationship question
    By txlibertygirl in forum Access
    Replies: 2
    Last Post: 10-13-2011, 08:59 PM
  5. Relationship Question on one-many
    By daveofgv in forum Database Design
    Replies: 2
    Last Post: 05-08-2011, 10:39 AM

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