having trouble with relationships (wife "included")

[Tramp]

In re-hashing this inventory control with my wife. And after setting up tables and a few forms to test the relationships i already had set up, it seems best if i add "catagories" into the mix. I know this is, in all likelyhood a "newbie" question, but i just cant seem to grasp how this mix would best be represented. Any suggestions or at least a gentle "nudge" in the correct direction would be greatly appriciated.

As it stands Tables and relations are as follows:
Table is dirty, and relations with wife are strained .....But seriously....

tblItems
ItemID (pk)
CatagoryID (number, required)

tblParts
PartID (pk)
CatagoryID (number, required)

tblBOM (Bill of Materials)
ItemID (pk1)
PartID (pk2)
Quantity (number, required)

As you can see, ther is a "many to many" relationship set up between the tables. I have referential integrety setup as well, with cascading updates and deletes.

My problem is that when I add the table
tblCatagories
Catagory(pk)
CatagoryID (auto number)

and try to create a one to many relation between tblParts[catagoryID] and tblCatagory[catagoryID] access complains that the existing table (tblParts) is the primary table in an existing relationship. And wont allow the new relationship to be created

 

[daveUK]

Tramp,

Delete the CategoryID fields from TblItems and TblParts. Access will probably tell you that you will have to delete the relationships first.

Then create TblCategories and go back to TblItems and TblParts and insert CategoryID as a FK.

Might not be so easy to sort the relationship out with your wife