Many parent records on a single child record

Question

Hi everyone,I'm having some issues building the following: I need to build a table where a laboratory analyst can record analytical standards that they have prepared. I have one table that records reagent inventory and relevant reagent information like lot number, date received, expiration date, manufacturer, etc. For analytical standards that use only one reagent, this is simple to build with a one-to-many relationship. However, some standards are made with multiple different reagents. How do I get multiple parent records on a single child record? Any help would be appreciated!

MarkShnierYou · Accepted Answer

np, This is a classic Many to Many relationship.

One Analytical Standards uses many Regeant Inventories

but also

One Regeant Inventory is used in Many Analytical Standards

so just create that middle table called perhaps "Analytical Standard Regeants". Initially no fields.

Make those two relationships I described

One Analytical Standards has many Analytical Standard Regeants

One Regeatnt Inventory has many Analytical Standard Regeants

and lookup appropriate fields from the two respective Parent tables down to that join table. Set the Proxy fields for the two related fields for Related Inventory and Related Analytical Standards. (that s a field proierty of those to "Related Parents fields".

The go to that Join table and add in any extra fields you need, like presumably Qty, unit of Measure and another "baking" instructions for the recipe.

Then on the form for Analytical Standards, you will put an embedded report of the Analytical Standard Reagents so you can see the recipe when you view a Analytical Standard record. Then similarly on the Reagent Inventory Form it's probably useful to put the report link for Analytical Standard Reagents as an embedded report so you can see which recipes (ie Analytical Standards) make use of that Reagent Inventory.

Sign up

Login to the community