Formulation Database: issues with getting specific data with query

[miguellopez12]

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.

I'm am also open to a different database design. I can always share my database to help other look at it and tell me what I am doing wrong. I really want to learn this stuff.


Any help would be appreciated.

[Micron]

Databases and spreadsheets are 2 different things, and spreadsheet design is bad for database work. Sheets are wide and short; tables are narrow and tall. Data related to an entity goes into its own table; related data goes in related tables and you match up with joins on foreign and primary keys for one to many situations. For many to many, you introduce a "middle" table, commonly known as a junction table. I can't give too much focused guidance because I have no knowledge of your process, but it sure looks like you're off to a bad start - too many tables, tables storing images, and perhaps multi-value fields as well.

Regardless, the only way you can eliminate the zeros is with a bunch of UNION queries, which is a sure sign of improper design. What you ought to do is research db normalization. Sorry!

[ssanfu]

Not to mention spaces in object names and special characters in names (%,#) .........

Any chance you could/would post your dB?


And welcome to the forum....

[Dave14867]

I have a database that does something similar but I use it for inventory control and traceability. I designed it for work and for any given "recipe" that is used to create the final product, it will calculate the required amount need of each part that goes into it. Is that what you are looking for? If so, I can explain how I designed mine.

[miguellopez12]

Thank you. I have looked up a couple videos on normalization. That is the main reason that I separated everything into their own table with a primary key attached. I am here to learn everything I can and your explanation really helped me understand when to use a many to many relationship. I will do my best to clean this up.

[miguellopez12]

How would I go about posting the the dB? I was wondering why the I was having issues when importing the excel spreadsheets into the database. I will remove the spaces and characters.

[miguellopez12]

That is pretty similar. My database needs to have a specific recipe for each formulation and the amounts needed to go into each formulation.

[Dave14867]

I have sent you a PM