Results 1 to 8 of 8
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Many to Many Self Relation (Access 2003)

    I have db that is concerend with the teaching of units in a university. Units are sometimes taught together, so that units ABC12A and ABC12B might be listed as separate units, but are actually taught together in the one room by the same person.

    These units are described as having shared teaching. I want to flag this so that if I look at either unit I see that it is shared with the other, but I don't want to enter data more than once. Potentially, more than 2 units might share teaching.

    So I have created another table, which just has two unit codes (firstunit and secondunit) to indicate that these two units share teaching.

    I can use a subform to enter shared teaching codes for a unit, but if I go to the other units, they will not show up, because the units codes will be in the reverse place for the parent/child relationship to show them.

    In the attachment, I have two subforms with the parent/child relationships reversed in the second. All shared units now show in one for or the other, depending on where they were created.

    One idea is to separate the displaying of shared codes from the process of adding them. A union query could be used to display the right units, but would stop adding/deleting.

    Can anyone suggest a neat solution to this?
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Many to Many Self Relation (Access 2003)

    I would use a slightly different setup, indeed separating editing the shared teaching codes from displaying them. I created a table tblGroups with an ID for each group of shared units, and an intermediate table tblPools that defines the members of the groups. The advantage is that all shared codes will be listed for each code. See attached version.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Many to Many Self Relation (Access 2003)

    Here's another version. In fact you don't need a many-to-many relationship. Each unit can be member of only one "group", so you can add a field to the units table that identifies the group to which the unit belongs (if any).

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Many to Many Self Relation (Access 2003)

    Thanks Hans.

    You are correct that each unit can belong to only one group so it is not a many-to-many relationship.

    I attach my current version, which seems to me to work well except for one thing.

    On the units form, I have a listbox that displays units that share teaching with the current one.

    If I share a unit with one that currently does not share with anyone else, it does not appear in the listbox until I go to a different record then return. If the unit already had a share, the new share appears immediately.

    I requery the listbox in the oncurrent event, and I run the oncurrent event after adding or deleting a share. I am sure the event actually fires.
    Regards
    John



  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Many to Many Self Relation (Access 2003)

    Hi John
    Is units3.mdb the correct database to use, if so, mine works fine.
    If you add a share it shows in the list box called SharedList.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Many to Many Self Relation (Access 2003)

    If you share a unit with one that currently doesn't share with anyone else, you must save the record:

    ' now put the other unit in the group
    sql = "Update tblUnits set tblUnits.PoolID = ...
    DoCmd.RunSQL sql
    RunCommand acCmdSaveRecord

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Many to Many Self Relation (Access 2003)

    Actually there are two cases that need the record to be saved, so I added the save command immediately before calling Form_Current.

    Thanks again.
    Regards
    John



  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Many to Many Self Relation (Access 2003)

    Patt

    If I find a unit that is not currently shared with anything, and share it the shared unit(s) don't appear in the listbox immediately.

    Whereas in units4.mdb (which has a save command) they do.
    Regards
    John



Posting Permissions

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