Table relationships and Primary keys

[wstokarz]

Hello all,
I'm trying to get a one to many relationship or many-to-many
relationship between three tables. Each table is embedded in the other. The
process i widh to accomplish is to have my primary table with the primary key
pull data from another table, the linked table will then pull data froma
nother table linked to it. My problem is I need to be able to have indexed
keys but multiple entries. Basically The first table can have only one work
order, the next table can have many item numbers based on the work order, the
last table many layer numbers based on both the work order and the item
number.
I have tried junction tables and using primary keys. Neither have worked
for me. Any ideas will be very helpful.


Thanks Bill

 

[Steve Schapel]

Bill,

This is not a many-to-many situation. If I understand you correctly,
something like this should be what you need...

Table: WorkOrders
WorkOrderID
<other WO-specific data>

Table: Items
ItemNumber
WorkOrderID
<other item-specific data>

Table: Layers
LayerNumber
ItemNumber
<other layer data>

I think the main point here is that your Layers table doesn't need to
directly reference the WorkOrders table. It just needs to relate to the
Items table, and once you know the Item than you automatically also know
the WorkOrder.

 

[wstokarz]

Thanks for the help.

I tried to link the tables as expressed however when I remove the Primary
key from the item table and the layer table the recordset becomes "non
updateable". I can not use a primary key in these tables because there can be
many items for one work id and many layers for both the work id and the item
number. Perhaps
I have my relationships wrong but I have treid many differnt approches.