Many-to-many relation ship inside one table

JoeyJoystick

Registered User.
Local time
Today, 11:29
Joined
Jul 23, 2015
Messages
15
Hi all,

I have a table with all my contacts. Manufacturers, distributers and outlet shops. I want to be able to make relationsships that show which manufacturers sell to which distributers and outlet shops, but at the same time I also want to be able to see which manufacturers are being sold by a particular outlet shop or distributer.

I am reading everywhere that you need to make a table to link the 2 tables together for every one relationship you have and this I understand. Most of the time it is relations between 2 tables that you want to establish. But in this case all these companies are in the same table. Can I still make a many-to-many relations ship between different items that are all in the same table using one single extra table that holds these relationships?

Thanks

Joost (Joey)
 
you dont HAVE to make relationships.
I would only make them for parent-child tables. Like Order->OrderDetails.
where you dont want users to delete an tOrders.ORDERID because all the detail record would get orphaned.

But you dont need a relationship for Manufacturers ->distributers.
You can but its not vital to the database.
 
It appears to me that there is more to your post than a list of Contacts. The mention of manufacturers, distributers and outlet shops suggest some sort of business.
I recommend you work through one of these tutorials from RogersAccessLibrary to get a good understanding of database design, tables and relationships. You will learn a process to go from a business description to a database that supports the business. The process can be applied to any database/business.
There are solutions included in the tutorials. You must work through the tutorial to get the benefits.
ERD
Catering
Widgets

You need a clear understanding of what is involved including the scope. You can design the big picture, but only develop the critical pieces now, and others later --they will all fit in your bigger picture.

Good luck.
 
Last edited:
Hi All,

First of all thanks for your help and guidance.

Just a note on the side. I am not doing this for work. I want to learn access and I figured the best way of doing this is picking up a practical project. Building model airplanes is a hobby of mine. However when you are looking for particular parts I Always end up spending a lot of time searching for them, so I have decided to make a comprehensive database to show just this. Who makes what, where can I buy it and of course most importantly, a user interface that allows searching this all. If I think it end up with something usefull, I may approach a model airplane forum and ask them to host it. for free that is of course. May benefit others as well. But at the pace I am going right now that will take an other year I guess...


Like Always when you jump into deep, it involves a little more than what I had initially participated though.

Anyway, I will check out these tutorials that you had mentioned, and obviously I will also check out what this parent-child table thing is about. Again thanks a lot for your help and guidance.

Joost (Joey)
 
Hm. I seem to remember vaguely seeig a horse breeding database relationship view. One table called tblHorses lists all the horses. Now, for breeding you need to know which horse is the parent of which other horse. To do this, the database added the tblHorses table twice (or thrice?) to the relationship view. the table had FatherID and MotherID which linked back on itself to HorseID in the same table.

So, basically if u look up geaneology type databases that may help you. I'm afraid I don't remember any other details, but that could help u already anyway.
 
(Except the database used proper horse terms instead of "mother" and "father".)
 
And I have no idea if that's a legitimate or sensible way to do it, but it is thinking outside the box a bit!
 

Users who are viewing this thread

Back
Top Bottom