Building a better mouse trap :)

[ldodge]

Hi all--
I work at a creamery and as of now we use a Excel Spreadsheet to record our lab test results. I would like to create a database to do this for efficiency reasons.
Attached is a pdf of the spreadsheet (gulp -- please understand I am better versed in Excel than Access -- but with your help hopefully that will change ). On the first page, there is a condensed list of our product categores and individual products. On the second sheet is a small portion of our spreadsheet. Currently as we test, say cottage cheese; we find the corresponding row for that product and then start to fill in the cells as test results are finished. The yellow columns (I shorted some columns to save space) have equations that show the tech at a glance if the results of the test are in spec or not (the last column is just an average of weights sampled).
My question (plea) is where to start, how to configure this, what would you do if this project was given to you -- running out of the room screaming is not an option hehehe. I would be greatly appreciative for ANY thoughts (the more details -- the better) or ideas from all of you. Thanks in advance.
LCD

[jzwp11]

First, would be to define the products that you sell. Each product belongs to a product category, so we need a table to hold those categories

tblCategory
-pkCatID primary key, autonumber
-txtCategoryName

The above table would hold the product categories: yogurt, cottage cheese, sour cream etc. as records.

We need a table to hold the products. I assume that a product can only belong to one product category. If that is not the case, then a different design will be necessary. The following table assumes that a product can only belong to 1 category.

tblProducts
-pkProductID primary key, autonumber
-ProductNo (a product number this could be a number field or text depending on your business process)
-fkCatID foreign key to tblCategory to tell you to which category the product belongs


You then use various descriptors (salted,light, regular, 1%, red pepper, tomato, etc.) to define each product, so a table to hold those definitions/descriptors

tblDefinitions
-pkDefID primary key, autonumber
-txtDefinition



A product can have many of these definitions/descriptors in order to fully describe the product

tblProductDefinitions
-pkProdDefID primary key, autonumber
-fkProductID foreign key to tblProducts
-fkDefID foreign key to tblDefinitions

Your lab conducts tests, so we have to have a table that holds all tests (each test is a record in the table). If you have a prescribed analysis method for the test such as an FDA method, ASTM method etc. you could have a field to list the specific method. Also, you might want to define the units of measure for each test for consistency in your results. You can create a table that holds all applicable units of measure and then reference the correct one with a foreign key in the test table.

tblTests
-pkTestID primary key, autonumber
-txtTestName
-txtTestMethod
-fkUOMID foreign key to tblUnitsOfMeasure

tblUnitsOfMeasure
-pkUOMID primary key, autonumber
-txtUOM

Now each product will require many tests and a test may apply to many products so we need a junction table between products and test

tblProductTests
-pkProdTestID primary key, autonumber
-fkProductID foreign key to tblProducts
-fkTestID foreign key to tblTests

Now for each product/test combination, you have a specification which appears to have an upper and lower limit (best as I can tell from your spreadsheet). Since each product/test combination has more than 1 limit, you have a one-to-many relationship and thus another table is needed

tblProductTestSpecs
-pkProdTestSpecID primary key, autonumber
-fkProdTestID foreign key to tblProductTests
-fkLimitTypeID foreign key to tblLimitType (field that describes whether it is the upper limit or lower limit)
-spLimitValue (the actual specification limit)

tblLimitType (2 records: Upper, Lower)
-pkLimitTypeID primary key, autonumber
-txtLimitType

I was a little confused by your IF() functions in the table/spreadsheet you provided:

=IF(J11>1.15,"NO GO",IF(J11>0.84,"GOOD",IF(J11>0.05,"NO GO","")))

If I look at the above it tells me the test result is good if the value is between 0.84 and 1.15. If the value is less than or equal to 0.05 then you return a blank cell--what is the purpose of that?

In terms of your tables, you would enter the 0.84 and 1.15 as records (2) related to the particular product/test combination. I'm not sure what to do with the 0.05.

All of the above tables are just to define the products, the test required for those products and the applicable specifications.

Now we have to create tables to capture the results of the actual testing. Do you do the testing on a batch by batch basis? We'll need a little more detail what conditions trigger the testing relative to your production.

[ldodge]

Hi Kevin--
This is awesome (a bit overwhelming -- but the best way to eat an elephant is one bite at a time ), I will start to work on this hopefully this afternoon. As for the if statement, I put the <0.5 just so when there were no entries, the next cell would be empty. If I didn't add that all the "reporting cells" would always read "NO GO". Thanks again for your help, not the fun begins.

If anyone else has any thoughts, please don't feel shy about sharing them.
LCD

[jzwp11]

Who is Kevin? I'm Greg (jzwp11).

We still need to flush out capturing your actual lab results. Once you have had time to digest the info I provided in my first post, please let us know when you are ready for the next step.

[ldodge]

Sorry Greg--
It came up w/ Kevin@accessforums.net.
Thanks again for your help, I don't know how quickly I get this all done, but I will definitely look you up when I am done
LCD

[jzwp11]

You can just post back here, I should get an e-mail notification.

[ldodge]

sweet thanks
LCD

[ldodge]

Hello--
Is there a way I can send you an 820 KB file for you to review?
LCD

[boblarson]

Hello--
Is there a way I can send you an 820 KB file for you to review?
LCD

Can you not post it here? Just make sure to run COMPACT AND REPAIR first and then Zip the file by right-clicking on it and selecting SEND TO > COMPRESSED FOLDER and it should be under the max file size.

[ldodge]

Hi guys--
I have added a few more tables to this. Could someone tell me where to go from here? I need to have form that is filled out and all these tables get populated

[jzwp11]

You'll need to make sure that you enforce referential integrity on your relationships in the relationship window. Also, why do you have two tables of fruit flavors with essentially the same structure? You will need to explain the purpose of those two tables.

[ldodge]

Will do. The reason I put two different fruit flavors is that we have one line that uses their specialty flavors that no one else uses. I have a detail on the table for this.
Could you give me a good rule of thumb when to create a table and when to just add a record to an existing table. It seems I am making tables for every little detail.
Thanks again
LCD

[jzwp11]

What are the fruit flavors? Are they products or ingredients used in products?

Could you give me a good rule of thumb when to create a table and when to just add a record to an existing table. It seems I am making tables for every little detail.

One rule of thumb is that like data should be in 1 table, so the fruit flavors should not be in separate tables.

In general things that are similar belong in a table for example: people would be in a table, products would be in a table, materials/ingredients (used to make products) would be in a table, parts would be in a table etc.

I view a table as generic and leave the specifics to the records in the table.

[ldodge]

So it would be ok to put all the flavors in the "tblDefinitions" I want to be able to have a look up (actual a look up within a look up). Pick from the category ie yogurt then another lookup will let you pic say strawberry. The two fruit tables are because they is our yogurt and then there is another private label we make -- their formula, their ingredients, etc. so basically they are two different categories

[jzwp11]

So it would be ok to put all the flavors in the "tblDefinitions"

If it is something that is used to define the product then it should go in tblDefinitions and then linked back to the product to which it refers via tblProductDefinitions.