Solved

How should I set up the many-to-many relationships?

Posted on 2016-09-28
13
37 Views
Last Modified: 2016-11-08
In the Issues table, which is the main table, I want to maintain referential integrity and be able to join multiple Defects_DCRs to one issue and/or multiple RFCs to one issue. With the current structure, I'm not sure where to place the intervening join tables or if one of the current tables, possibly the Issues table, should be the join table.

I've seen articles on this, but couldn't find one during a half hour to an hour of searching. I suppose I wasn't using the right key words. If you would also explain the logic behind the solution in a step by step format, that would help me better understand for future reference. Thank you!
RFCsTest1.accdb
0
Comment
Question by:David Bigelow
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41820152
It seems you have a 1:M on issues:defects
and 1:M on issues:RFCs already
No more relationships or tables are needed.
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 41820269
I thought the above question would help me meet my end goal, but maybe not. My desire is to create a Defect_DCR multi-value field and an RFC multi-value field in a form (I see I didn't include a form with the sample database) with checkboxes to mark each Defect_DCR or RFC that is associated with the issue. My understanding is that the Access lookup tables are actually hidden many-to-many tables.

I could use the built in lookup tables function, but I wanted to maintain more control on being able to sort and use the data. I've attached the test database with a form.

Should I post a new question as, "How do I set up many-to-many relationships for multi value fields?"?
RFCsTest1.accdb
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41820276
A M:M relationship is implemented with an 'intermediate' table holding keys from the other 2. (and a compound key is uniqueness is required)
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 41820396
According to this forum post, http://stackoverflow.com/questions/13505838/alternative-to-multi-valued-fields-in-ms-access, there really is no easy way to replicate lookup tables. I read banter about how it is better to make your own, but am not finding any examples on how to do it, which is why I came to the experts.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41820469
I've attached a sample database that shows how to implement a m-m relationship.
ManyToMany.zip
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 41820546
Thank you, Pat.

Right now, I'm seeing I need to go back to the user to clarify what is needed.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:hnasr
ID: 41820568
Simple example:
Tables:
     Students
SID Name
1 n1
2 n2
3 n3

     Courses
CID desc
1 d1
2 d2
3 d3
Relation between Students and Courses is M:M, because each student may enroll in more that 1 course, and each course can be taken by more than 1 student.
To use M:M we create a new table:
     Student_Course
SID CID desc
1      1    ---
1      2    ---
2      1    ---
2      3    ---
3      2    ---
3      3    ---

Relationships
Students:SID>----M:1-------- SID:Students_Courses:CID ------1:M-------< CID:Courses
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 41820598
I feel like I've steered the conversation off course with my initial request. Basically, I want to be able to assign more than one defect_DCR to the Issue. (I was trying to say that further up in the post). Currently, it is the other way around. More than one issue can be assigned to a defect_DCR

Update. It's starting to make sense.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41820649
The Parent's primary key goes into the Child table where it is referred to as a foreign key.  If your relationship is backwards, change the tables as I described.  If you have existing data that you don't want to loose, you'll need to do some cleanup first.  Add the correct foreign key but don't delete the incorrect one.  Join on the incorrect relationship and update the new foreign key with the primary key from the old table.  Once you are certain that the records are linked correctly, delete the incorrect foreign key.  Then delete the existing relationship and recreate it.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41874228
Why was my working sample database insufficient?
0
 
LVL 1

Author Closing Comment

by:David Bigelow
ID: 41877414
Hello Pat,
Thank you for posting. I had to go back and reconfigure my foundational table relationships since I didn't quite understand how they should be when I intially created the database. After that, I never got back to this issue. But, you answer looks like it is what I'm looking for. I appreciate the sample you provided. It is easy to understand and follow.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41878837
You're welcome.  Glad it helped.

Vote early and if you're in (or near) a state that doesn't check ID's, vote often.
1

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now