How To: Many-to-Many relationship where the data is from 1 table?

[Neil]

Hello all,

I have a database that keeps records on Companies. What I need to do is know
which company can supply other companies products. For example: I could have
the following in my Company Table

Company ID Company Name Makes/Manufactures
1 Mikes Cheeses Cheese
2 Sparkling Cola Cola Drink
3 JT's Exports
4 Holders International

From the above, 2 of these companies could be classed as manufacturers and
the 2nd two could be classed as supplier of products or dealers for example.
I would like to keep a track of who supplies what. I am trying to get my
head around this and thinking logcally, many companies would be able to link
to many other companies in the same table. To make this work, i think all i
would need is a Junction table that has the Company ID field twice as the
foreign keys and this would then link back to the primary Key of the company
table. Obviously i would create a form with a sub form where you can select
who supplies what to who after all known customers have been entered etc.
Would this work or is there some other way of doing it??

Junction Table would look something like:

Company 1 ID Company 2 ID
3 1
3 2
4 2

In other words, JT's Exports can supply Mikes Cheeses Cheese and Sparkling
Colas cola drink. Holders International can only supply Sparkling Colas cola
drink.

TIA,

Neil.

 

[Tim Ferguson]

Junction Table would look something like:

Company 1 ID Company 2 ID
3 1
3 2
4 2

Yes that is fine, except I would be kind to myself and call the table
"CanSupply" and the columns something like "Supplier" and "Customer".

Remember to make both columns required, remove the DefaultValue from each,
and set the PK to the combination of (Supplier, Customer).

B Wishes


Tim F

 

[Neil]

Thank for your reply Tim.

I'll give it a try now I know this is the way to go, Cheers for the Supplier
and Customer tip - mind was getting too confused to even think of something
simple like that .

Thanks again,

Neil.