Many-to-Many relationship help

[mobile2]

I am using MS Access 97 and think I need to create a many-to-many relationship. The database is in the early stages of design. At the moment I have 2 tables, one called SUPPLIERS containing details of office suppliers, it has a primary key field SupplierID. The other table is called TENDERS containing details of requests that staff have made for office equipment etc. which also has a primary key field TenderID. From these tables there have been queries and forms created so that new suppliers and new tenders can be input. The next stage is to relate these tables so that when a user inputs a new tender they can also assign a number of suppliers(around 4 or 5)to that particular tender. Can you help me progress on this?

 

[lespaul]

You will need another table: tblTenderSuppliers

It should have:
TendSuppID (autonumber, optional; if used, then PK)
TenderID(FK to tblTenders)
SupplierID(FK to tblSuppliers)

if you choose not to use the autonumber as the PK then the combination of each TenderID and SupplierID will make up the PK.

You will want to have a form that allows the users to select the TenderID from maybe a combo box, then open a subform that allows them to select the suppliers. The users should be able to get to the Add Tender and Add Supplier forms from this entry form.

HTH

Leslie

 

[LittleSmudge]

It should have:
TendSuppID (autonumber, optional; if used, then PK)
TenderID(FK to tblTenders)
SupplierID(FK to tblSuppliers)

if you choose not to use the autonumber as the PK then the combination of each TenderID and SupplierID will make up the PK.

The SECOND option above is absolutely the preferred method.
If you do not implement the second option then you have to find some other way of making sure that a user does not allocate a supplier to the same tender more than once.

It's far simpler to let Access do all that work for you.

The separate PK in the first option adds no value at all and just takes up memory and processing time.


'ope-that-'elps.






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lespaul]

Actually, if you use the autonumber, you can also create an index on the TenderID, SupplierID and set Allow Duplicates to 'No'. That way the autonumber is the PK, but you also have a unique set of Tenders and suppliers.

leslie

 

[mobile2]

Thank you all for your sound advice.

 

[LittleSmudge]

True - Leslie
But then all you've done is added an additional INDEX to the table to do exactly the same thing as is achieved with the two field table.

Having the two FKs as a combined Prime Key still gives you a simple ( simple enough anyway ) UNIQUE reference to any particular record so you don't loose anything except wasted memory space and process tie up time.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[JeremyNYC]

LittleSmudge,

I actually prefer to use the autonumber as the PK and the two-field index to insure no improper duplicates. I'm positive your way is the more sound way to go as far as db theory goes, but having a single-field PK sure makes a lot of things easy--especially if you have a table that is in effect subdata of the table in question.

Mobile,

I guess this is obvious, but all this is a big reason to make sure you design your tables thoroughly before you start building your queries and forms.



Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.