Relate one field to multiple fields

[Zydeceltico]

I just noticed that tblAssemblyItems is not correctly defined. There MUST be a unique primary key. This is a combosite key and will be Assembly_FK + Part_FK. To do this, open the table in design view and using shift-click, select both fields. Then press the key button. As it stands, you will get an error because there are duplicates so you will need to fix the duplicates first.

Sorry, I should have clarified the PK when I made the suggestion.

Thanks Pat. I know I have said in the past that any assembly will always only be two parts, but as I am creating a unique composite key across two fields I immediately wonder how I will handle the day when we do in fact have an assembly that has three parts and my unique composite key is only across two fields.

 

[Zydeceltico]

I see 62 EndItems but only 61 Assemblies which I don't quite understand.
It's possible that EndItem (Assembly_PK 62) is planned, but doesn't yet exist.
My thinking is create an EndItem and then assign Parts ---completing the EndItems as you go so numbers are aligned (all enditems are assemblyItems).


My first thought was to copy the AssemblyItems to XXX, then delete all records from AssemblyItems, then restructure AssemblyItems to have composite PK(Assembly_PK + Part_PK). Then set up referential integrity, then repopulate AssemblyItems from XXX...
the idea is to let the database do the referential integrity checking.

Just some thoughts for consideration.

Cleaned it up. Thank you Jdraw.

After giving my "new parts and assemblies" question some more consideration, I realized I'll just do it the way it would be done in real life in the engineering department. I'll make two new unique parts records (or some new combination of parts) then I'll create a record for my new assembly. Then I'll go to tblAssemblyItems and make a new entry that combines the two. A simple logic but I think that it works.