1-n relation betweens tables having each a multiple primary key field

[ddgermany]

Hi

Background and goals
I'm designing a database for a manufacturing company wanting to define which items can be produced by witch tools.
The database will be replicated on 3 production sites (There is no fast network connections)

• A tool can have have several inserts
• An insert can only be used in one tool
• An insert can produce several different items and an item can be produced on several tools.

Tables

• 1 tool table with a tool id as primary key
• 1 insert table with the fields tool id and insert id defined as a primary key
• 1 item table with an item id as primary key
• 1 item-insert table having the fields tools, inserts and items as primary key.

Relation

• 1-n between tool and insert
• 1-n between item and insert item

See screenshot underneath
Attachment 2195

Problem
I have to set a 1-n relation based on the fields tool and insert of the table insert and items-insert.
Access just refuse to do so and send me the followinf error message :
"No unique index found for referenced field of primary table. "

I've considered the Article from Microsoft concerning relation with multiple field primary key. http://support.microsoft.com/kb/155514
I've tried differents set of order of the fields and indexes but it did not solve my problem. (I've succeed on linking two multiple-field primary key tables with a 1-1 relation)

Work arround
I've redesigned the database using a single field with a random generated integer primary key on the table insert. (See below)
Attachment 2194
and I set on the field tool and insert of the table inserts an index checking unicity.
It works but, I'm concerned about possible conflicts while adding records on the different sites and then synchronise them. I'm expecting about one million records on the inserts/item table and I will be able to synchronise after each sites adds 30-50000 records.
I don't have this issue on the original design as each sites can only works on their own tools.

Questions :
Can somebody tell me why I can't create in Access the original design?
Is there another way to overcome that problem? (I would like to avoid any VBA based solution)?

Many thanks in advance

David

[jzwp11]

Since you have already established a relationship between the tool and the insert, you do not need to reference the tool in the insert-item table. See the attached database as an example.