chemical manufacturing research database

[marymargaret]

I'm back in the work force after some time off and back to developing with Access ( I couldn't remember my old username, lol). I'm creating a project database where each project has different experimental formulas (try1, try2 try 3 and so on) and each "try" has several ingredients. The ingredients come from a dropdown list. I'm having a hard time figuring out how to structure this. Each try will also have experimental results.

Thank you!

 

[Ranman256]

tIngredients tbl
---------
IngredID , autonum
IngredientName


tTry tbl
---------
*TryID (text or numeric depending how you want to use the Try format)
TryDate
other detail fields
etc


tTryIngredients tbl
---------------
*TryID
*IngredID


*=keyed

make a form for data entry for fTry: frmTry
subform bound to :tTryIngredients

LinkMasterFields= TryID
LinkChildFields=TryID

a list box user can dbl-click to add IngredientID to the subform.

 

[LarryE]

I'm back in the work force after some time off and back to developing with Access ( I couldn't remember my old username, lol). I'm creating a project database where each project has different experimental formulas (try1, try2 try 3 and so on) and each "try" has several ingredients. The ingredients come from a dropdown list. I'm having a hard time figuring out how to structure this. Each try will also have experimental results.

Thank you!

Given only what you have told us, you might start with the following table and field design:

Of course you will need to add the fields for each table as required.
Here's the file

 

[marymargaret]

Thank you both. So far I've been noodling with Ranman256's suggestions. I realized that there can be instances where the raw material is repeated in a particular formula, so that's an issue.

Larry, I'm off to look at your suggestion!

Thanks so much!

 

[LarryE]

I realized that there can be instances where the raw material is repeated in a particular formula, so that's an issue.

No not really. Add as many ingredients as you need for each experiment formula. What that ingredient is will make no difference to ACCESS. Each ingredient record has its own unique IngredientID.

 

[LarryE]

Design is probably better like this. Each Experiment may have multiple ingredients but only 1 result.

 

[jdraw]

marymargaret,
Do you have any sample data or description of a proposed experiment? It might help put context on the issue.

 

[Pat Hartman]

Getting closer. But tblIngredient stands alone. It does not contain a FK to the experiment. There needs to be a junction table between experiment and ingredient and that is the result table.

So remove ExperimentIDFK from Ingredient And add IngredientIDFK to tblResult and reconnect the lines.

 

[GaP42]

Not sure of the rationale for your suggestion @Pat Hartman ... If an experiment can involve multiple ingredients in different proportions as selected from an ingredients catalog, and the experiment (try as in OP) has a result then why would you associate the result through the ingredients? Larrys post #6 appears OK, apart from a catalog from which ingredients might be selected for each experiment.

 

[LarryE]

The OP probably needs a standalone Raw Material table that holds all the material information used in each Ingredient.

 

[CarlettoFed]

Perhaps the most correct structure is the following.

 

[Pat Hartman]

@CarlettoFed Thanks for drawing the picture.
still not there. The junction table holds the results.

 

[GaP42]

The tblExperimentIngredients is the junction table in @CarlettoFed 's design would not be expected to hold the result of an experiment. If it did there would potentially be a Result for each ingredient associated with an experiment, rather than the overall specific combination of ingredients that were used in the experiment.
However Pat has been involved in industry that was concerned with producing / formulating (hair?) products and may have some other experiences in relation to this. Perhaps it is about the "tweaking" of the amount of an ingredient in a formulation to get the optimal result?
@marymargaret - when a result is recorded for an experiment, is only one result (report) recorded, or does it contain multiple elements/observations (as text and/or measurement?)

 

[Pat Hartman]

@GaP42 No, you're correct. I was trying to play bridge while I looked at the schema. The result belongs in the Experiment table so @CarlettoFed got it right. Sorry, reading posts didn't help my bridge game either

 

[CarlettoFed]

The result of an experiment is in the tblExperiments table (ResultConclusion field) and not, as you reported, in the tblExperimentIngredients table.
If the quantity of an Ingredient changes, it is a new experiment.

 

[The_Doc_Man]

I'm going to add that it is a poor researcher who doesn't repeat an experiment. Therefore, unless the experiment ID covers multiple repetitions of a given experiment, there is still something missing.

 

[GaP42]

I would expect that that would be handled - the experiments (records) are each independent of the others (within a project), so each experiment can be repeated (as in use the same set of ingredients as required), with results noted. The repetition would be evident in the report in which a selection is based/ordered by some factor - like an ingredient by quantity - for a project. However repetitions occur for the range of combined ingredients ensuring no one result is a statistical fluke
It would be unusual to only record a "result" for an experiment and not a series of observations / measurements that then need to be analysed to generate a conclusion. Hence the question in post #13. That potentially could be quite complex, depending on business needs
Projects themselves may have an internal hierarchy which could be supported with an appropriate FK to the Parent Project.
If needed, perhaps an IsRepetition flag or a Repetitionof FK to the original (Parent) experiment in the experiment table could be advised @The_Doc_Man ? The collation of the results from each I think is basically the same suggested report.

 

[The_Doc_Man]

True enough. When I did my doctoral research a few moons ago, the observables were pretty complex. A reaction rate was just ONE of the results I watched for.

 

[The_Doc_Man]

@jackzoe - your post was totally inappropriate and also violates site advertising rules. I am deleting it from public view. If you want to post here, keep with the theme of the post to which you are replying unless you are in either the political section or the non-technical section.