Access Relationship or Table issue

[ED55]

Here is my situation:

I am trying to create a database to keep track of RFQ'S (REQUEST FOR QUOTES) that we send to multiple different suppliers weekly. Each RFQ has part numbers (there can be 1 to infinity)in one RFQ. I want to keep track of the status of each part number. Basically, I would like for people to be able to come into the database, lookup a part number and see the status of this part number according to each supplier, so each RFQ (Since one RFQ can on be related to one supplier). On part number can be part of multiple RFQ's. But there is only one part number with the same supplier. Also each part number depending on the supplier has a certain status.


My issue:

I can't seem to find how to program the relationship, or even the tables to create this database. At first I had 2 table one for RFQ'S and one for Part Numbers. Each had a primary key (RFQ'S=RFQ ID and PART NUMBERS= Part number) and RFD ID field was in both tables as I had a one to many relationship with RFQ Id fields. BUT this did not permit me to have multiple part numbers since part number field was a primary key. Also since each part number has a specific status depending on the supplier (RFQ) associated to it, this didn't work.

Please help me!

Thank you in advance,

Emma

[PhilOfWalton]

Emma may I suggest

A table of Suppliers with SupplierID, SupplierName etc

A table of SupplierPartNos with SupplierPartNoID, SupplierPartNoDescription and SupplierID.

Now your RFQ table should have RFQID, RFQDate and SupplierID.

Then you need a table to link the RFQ with the SupplierPartNo So RFQID and SupplierPartNoID.

Basically have a form to create the RFQ, with a Combo Box to select the supplier. Having selected the supplier, open up a list box listing all the part nos and descriptions from that supplier and pick the ones you want for the RFQ.

That should be a starter for you.

I have a feeling that you may need an additional table listing Your part Nos & Description to find the appropriate supplier.

Phil