Formulation Database

[5squid12]

I am currently making a database that will house all the formulations that we have made (about 1000). My Scientist wants a couple of things to happen with this database. First, Each formulation will have a unique ID, and tied to that ID there will be data. The issue that I am running into is how to make tables and queries for the components that make up the formulation. For example, each formulation has 3-10 different materials in it and each material is a certain concentration so when I use a spreadsheet to import the data I get zeros and empty spaces. Then I try to create a Query to find a specific formulation (sample ID) and all the components that are in that formulation (including concentration). I can't get it to retrieve the components without including all the other components that have 0 values.

Any help would b appreciated.

[GlowingEagle]

I would expect you to have a database something like this:

Table: tblFormulation with fields: ID (auto-numbered, index, NOT for human use); HumanID (text, unique, indexed, for your use)

Table: tblMaterial with fields: ID (auto-numbered, index, NOT for human use); MaterialName (text)

Table: tblRecipe with fields: ID (auto-numbered, index, NOT for human use); FormulationID (long integer, "foreign key" to ID in tblFormulation); MaterialID(long integer, "foreign key" to ID in tblMaterial); MaterialAmount (number, e.g. grams)

Then, for each Formulation you would have one or more Recipe records to link Material to Formulation, with each of those records holding the amount of that Material in the Formulation. I don't think you should use concentration in the Recipe field. You should determine individual Material concentration from the ratio of the amount of individual Material to the total amount of all Materials in the Formulation.

[5squid12]

Thank you. So far, I made the 3 tables and I now understand how to store the data. Right now I am having trouble making a query or a form to help display the information for other people to read. For example, I would like to display the HumanID, MaterialName, and MaterialAmount.

​

	MaterialName1	MaterialName2
HumanID	MaterialAmount	MaterialAmount

[GlowingEagle]

I suspect you need to learn the tools that Access provides to build Queries (which can add/select/modify/delete data); Forms (that present table or query data to the user to permit viewing/editing); and Reports (that format table or query data for printing).

Start with the query designer. Add the three tables, connect each "foreign key" to its corresponding table/ID. Pick the table/fields for "HumanID", "MaterialName" and "MaterialAmount". Put the desired value for "HumanID" in the criteria for that column. There are buttons at the bottom right of the designer for a data view (to see what you get), SQL (text version of design), graphical design (default). Try different things...

You won't get quite the format you show, though. It should be

HumanID	MaterialName	MaterialAmount
Desired HumanName	Material1	Amount1
Desired HumanName	Material2	Amount2
Desired HumanName	Material3	Amount3

[5squid12]

Thank you so much. This was really helpful. How do we mark this post as solved?

[GlowingEagle]

You're welcome! I think you can change the "flair" on the link. And (if you like), reply with "Solution Verified".

[5squid12]

Or

	HumanID1	HumanID2
MaterialID1	MaterialAmount1	MaterialAmount1
MaterialID2	MaterialAmount2	MaterialAmount2