locked
enter data for a join table in a many to many relationship RRS feed

  • Question

  • Hi,

    If you have a many to many relationship, and you create the intersecting table with the two primary keys of the M:M table, is there an easy way to populate the intersecting table if you already have substantial data in both tables and need to insert the data into the intersecting table?

    eg: Students M:M Classes

    I already have data in students and classes, now need to populate the intersecting table (say called enrolments) 

    So Students ---> Enrolments <------ Classes

    Any assistance would be appreciated.

    Thanks

    John



    JG

    Thursday, May 21, 2020 11:33 AM

Answers

  • You could use a main form based on Students with a subform based on Enrollments. Or a main form based on Classes with a subform based on Enrollments.

    See https://www.dropbox.com/s/ymmlkmu3qzuce6w/Many2Many.accdb?dl=1


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Thursday, May 21, 2020 11:55 AM
  • You might also like to take a look at StudentCourses.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates a number of possible interfaces for a many-to-many relationship type like this, though I would recommend using a convention form/subform.  For one thing this allows the relationship type to have non-key attributes such as Status in my case.  For a more developed interface take a look at StudentLog.zip in the same OneDrive folder.  This also includes attendances at classes.

    Ken Sheridan, Stafford, England

    Thursday, May 21, 2020 4:13 PM

All replies

  • You could use a main form based on Students with a subform based on Enrollments. Or a main form based on Classes with a subform based on Enrollments.

    See https://www.dropbox.com/s/ymmlkmu3qzuce6w/Many2Many.accdb?dl=1


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Thursday, May 21, 2020 11:55 AM
  • You might also like to take a look at StudentCourses.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates a number of possible interfaces for a many-to-many relationship type like this, though I would recommend using a convention form/subform.  For one thing this allows the relationship type to have non-key attributes such as Status in my case.  For a more developed interface take a look at StudentLog.zip in the same OneDrive folder.  This also includes attendances at classes.

    Ken Sheridan, Stafford, England

    Thursday, May 21, 2020 4:13 PM