Creating a form with a shared linked table

[Jude Shellito]

I am trying to create a form to input table information. The table record can contain multiple entries form a shared table, each entry into the shared table can contain multiple entries from another table which contains a unique record for each item. I think I set up the relations properly. I have attached the relationshiprelationships041014.zip for review. See "Lot info" table linked to "PP lot" and then this table linked to unique record in "Plate Prep" table.


I want to populate the shared table from the "Plate Prep" table. I can easily perform a single link but am struggling a bit with this. What is the best way to do this. Do I create a subform with the population of the shared table and then insert this into my main form? Any help would be appreciated.

[John_G]

Is the relationship between Lot_info and Plate Prep many-to-many, and
is the relationship between Lot_info and Core Drying many_to_many?
(Your relationship diagram shows that is the case)

If the answer to either of these really is "Yes", then the Join tables PP Lot and/or CD Lot are wrong.

The table Retaining plates has a field ID Lot Number in it - presumably that is a FK, but to what table? You have it linked to Plate Prep, so I can't tell if the relationship is wrong or the field is just named wrong.

The table Lot_Info contains fields ID PP and ID CD - these should not be there if there can be many PP Lot or CD Lot records for one Lot_Info record. Your relationship diagram shows that is the case.

Take a look at your relationships, and see if they really match your process.
For example - Can many different PP Lot's really relate to the same Plate Prep? (Maybe they can - but I can't tell because I don't know how your industrial process works).

Answers to these questions will help us straighten out your relationships. Remember that your relationships have to accurately model your business process.

HTH

John

[Jude Shellito]

OK let me explain how it should work then maybe we can back into how I defined the relationships. I will describe the PP relationship, if I get this correct then the CD will be the same. So yes we can have many PP numbers for each record in the "lot_info" main table. Each PP number should be a unique record in the Plate Prep table. (This should be a one to one I think)
The naming is a bit odd for the table retaining plates. That relationship should be correct as I can have many lot numbers for a single PP number.