Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   TechHelp   Forums   Help   Contact   Merch   Join   Order   Logon  
 
Back to Access Forum    Comments List
Upload Images   @Reply   Bookmark    Link   Email   Next Unseen 
Many to Many Relationships
Carl Byerley 
      
2 years ago
Just a simple question to help me understand.
If you need several many to many relationships involving some repeat table can you do them with one junction table (see example) or should you bring the table in again and keep them seperate. (as in second example)
Carl Byerley OP  @Reply  
      
2 years ago

Sami Shamma  @Reply  
             
2 years ago
Carl, it will be helpful if you say what it is that you are trying to achieve. Then, some of us will be able to point you in the correct direction.
Carl Byerley OP  @Reply  
      
2 years ago
To answer my question, first i posted the wrong image, which way is best or does it make a difference to create these relationships.
Carl Byerley OP  @Reply  
      
2 years ago

Juan C Rivera  @Reply  
            
2 years ago
Keep it simple you tend to get errors when trying to do to much and it makes it hard to troubleshoot.  you can always bring to together in another query.
Ludwig Willems  @Reply  
      
2 years ago
volgen :)
Richard Rost  @Reply  
          
2 years ago
You could use the same junction table for multiple relationships, but it's going to get confusing.
Carl Byerley OP  @Reply  
      
2 years ago
I have multiple Customers, Multiple Contacts, Multiple Ship To
Customers share Contacts and Ship To addresses
Is this the correct way to create the Junction Relationships.
Carl Byerley OP  @Reply  
      
2 years ago

Carl Byerley OP  @Reply  
      
2 years ago
If there is a better way, Please explain
Richard Rost  @Reply  
          
2 years ago
Does your ship-to address table need to be a many-to-many relationship? In other words, do you have different customers who share the same address? Usually, I make that a one-to-many relationship. If another customer happens to have the same address, then so be it. It's just in the database twice.
Carl Byerley OP  @Reply  
      
2 years ago
Yes some customers share the same shipping addresses. I am trying to set up the Cascading combo boxes on my order form. I select a customer then, it then fills in the customer data, then the second combo box lists the ship to addresses associated with that customer and fills in info when selected.
But yes, many customers have many ship to, and many customers share the same ship to with other customers.
Richard Rost  @Reply  
          
2 years ago
OK, then M2M is warranted. You might find it easier to use a separate junction table for it though. What you have there will TECHNICALLY work, but it's going to be more difficult to keep updated.
Carl Byerley OP  @Reply  
      
2 years ago
Thank you  Richard, that is the question I was trying to get answered in the beginning. To use a seperate Junction table
Richard Rost  @Reply  
          
2 years ago
You can get it to work with just a single table, but you're going to find it's more difficult that way. It's kind of like my helper table thing that I set up a while back, where you can use one table to store all of the little bits of information, like a list of name prefixes and suffixes, things of that nature. And you don't want to make separate little tiny tables for all of these. You can do the same thing with Junction tables, but it gets a lot more difficult to manage them all and keep track of what's what inside the table. So, since you typically tend to not have as many many-to-many relationships in a database, I just suggest making those individual tables. You'll save yourself some headache down the line.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.
 

Next Unseen

 
New Feature: Comment Live View
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2026 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/6/2026 4:27:55 AM. PLT: 1s