Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.
COURSE of the MONTH
11 days, 10 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How should I set up the many-to-many relationships?
Posted on 2016-09-28
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
4-
2
- +1
13 Comments
Active 3 days ago
It seems you have a 1:M on issues:defects
and 1:M on issues:RFCs already
No more relationships or tables are needed.
and 1:M on issues:RFCs already
No more relationships or tables are needed.
Active 1 day ago
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
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
Active 3 days ago
A M:M relationship is implemented with an 'intermediate' table holding keys from the other 2. (and a compound key is uniqueness is required)
Active 1 day ago
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.
Active today
I've attached a sample database that shows how to implement a m-m relationship.
ManyToMany.zip
ManyToMany.zip
Active 1 day ago
Thank you, Pat.
Right now, I'm seeing I need to go back to the user to clarify what is needed.
Right now, I'm seeing I need to go back to the user to clarify what is needed.
Active today
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------
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------
Active 1 day ago
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.
Update. It's starting to make sense.
Active today
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.
Active 1 day ago
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.
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.
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder.
But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page.
The problem with sub-reports and headings:
Add a dummy group to the sub report using the expression =1:
Set the “Repeat Section” property of the dummy…
Suggested Courses
Course of the Month11 days, 10 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.