Setting up table structure

[funk88]

I am in the planning stages of a database that will store testing results for products.

Each product will have a set of tests (test plan) performed on them. The test plan will be different for each product.

Each batch of product produced will have all of the tests from that products test plan performed on it. These results will need to be stored in a test results table.

I have thought of two different ways to achieve this:

1) tblTestPlan contains the following fields.
Product

Test01
TestType01
HiSpec01
LoSpec01
QualSpec01
TestSort01
TestActive01 (yes/no)
TestOnCoA01 (yes/no)
.
.
.
TestOnCoA30

The above will have the same set for up to 30 Tests. This gives me a very large table with about 8x30=240 Columns.

The tblTestResults has the following fields
Product
Date
Batch#
Test01Result
Test02Result
.
.
.
Test30Result

I have the Access knowledge to get this method to work. I am just concerned about the extra long tables.

2) I keep thinking that there is a better more efficient way to design the tables. I have tried to create a many to many relationship using a junction table to set up the Test Plan and I am able to create a form showing all of the tests for a particular product.

However, I do not know how to set up a table for the results of each test using this method.

I would appreciate any input you could give me with this problem.

 

[Minty]

You need to Normalise your tables.
https://sites.google.com/site/msacc...-access-parts-1-2-and-3-as-video-instructions

Any time you see a field name with a value of some type in it you know you are in trouble. (Eg Test1, Test2, Test3 etc.)

 

[The_Doc_Man]

Overview of a possible approach:

You have ONE table with individual tests in it, one test-index (as PK) plus other information about the nature of the test.

You have ONE table with each product in it, one product-index (as PK) plus other information about the product.

You have a JUNCTION TABLE (look up this keyword topic if you don't know it) that enumerates the condoned tests for a product using a compound PK of <test-index, product-index> plus any other notations appropriate for the test in this combination. This junction table MIGHT be as short as two fields if everything else you need to know about performing the tests is found in the test table or the product table - and this might well be the case.

Now for the results, you have to define how (or whether) you are sampling products via some quality-control "confidence level" scheme. It has been ages since I did one of those, but it is basically a test that says "If I do n1 tests out of a run of n2 products, I will have xx% confidence in the quality of the run." It's all based on sampling theory.

OK, so you have a test results table, which will probably include data such as product-index, test-index, some sort of run-index (or perhaps you might call it a batch-run identifier), and a sample number. The trick will be how to represent the test result if the different tests give back different kinds of answers. However, if the test table includes some kind of pass/fail criterion regardless of what the test actually is, you would then be able to report just "pass/fail" on each test entry. In that case, your test results table records would resemble <product-index, test-index, product-run, sample-number, T/F>.

Since you are still at the design phase, this might help you see where to go. On the other hand, I might have gone completely on a tangent to what you were thinking. But this is what popped into my head when I read over your problem description.

 

[funk88]

Thank you so much 'The_Doc_Man'. I have created the following tables:

tblProduct (product names)

tblTest (test names)

tblProductTestJunct (junction table for matching tests to each product)
(fields are 'ProductID' (PK), 'TestID' (PK), TestType,
LoSpec, HiSpec, QualSpec, TestAcive (y/n),
TestCoA (y/n)


tblSample (fields are SampleID (PK), ProductID, TestID, SampleNumber,
RetainNumber, Result)

Currently we fill out a lab analysis sheet when testing a product. We input this sheet into a word document and save for our records. So if we want to view the last five results for a product we open up the last five word docs and compare them. This can be a time consuming process.

This database will serve to store the results from these sheets for easy retrieval at a later date.

I feel that the testing data can be saved as a short text.

I am having no luck connecting the test plan data to the sample table. I have tried changing the relationships between these tables without any success.

I have attached a sample database. I appreciate your advice.

 

[funk88]

This is quite frustrating. I thought I was having some success but now I just have a big mess. I guess I just do not have a full enough understanding of table relationships to make this work. I will keep trying.

I have attached the latest version of my test database.

Thank you for any help you can provide.

 

[informer]

Hi funk88

here is a free soft to model: jfreesoft.com/JMerise/
conceptual data model (CDM)
Generate automatically ta Physical data model from the CDM

And to go further with normalization a wiki topic herehttpshttps://en.wikipedia.org/wiki/Database_normalization#Normal_forms

Good reading

 

[plog]

Looking at your database, here's what I see:

1. Use notes. In design view of the table, there is a field called 'Description (Optional)', don't take the (Optional) part as a recommendation--fill out some notes on what each field does. As an outsider to your organization, I have no idea what [QualSpec], [TestCoA] or any other fields capture. If I was an insider, I'd still like to know if those fields are what I assume they are or of any kinks in the system that you had to employ.

2. Tables with 1 real field (autonumber primary keys aren't real data fields). You shouldn't have tables that act simply as lists. That means tblTestType and as it stands tblTest are unnecessary. Instead of storing their ID in external tables, you would simply store the text that it relates to.

3. Cirlcular relationships. In your realtionship tool, you don't explicitly have one, but by having TestID in tblSampleTest you effectively have one. There should be only 1 path between tables, if tblSampleTest.TestID relates to tblTest you have made a loop in your relationships and that is wrong.

4. Samples? In your posts you never mentioned samples (you did mention batches). I think I understand how things work, and if I do, your realtionslhip between tblProduct and tblProductTestJunct is incorrect. I believe tblProductTestJunct should be linked to tblSample instead. Doing so, would also eliminate tblSampleTest and avoid that ciruclar loop I mentioned in #3.

5. tblTest should probably have TestType. Again, don't fully understand your process/data, but I would think a TestType would be a property of a test, right? If so, it sounds like it should go into tblTest.

6. Better sample data. It would make it litter easier to understand and put your data together if it wasn't so genericized. If I could see all possible values for certain fields ([Result], [HiSpec], etc.) it would help as well.

 

[funk88]

Thank you Plog.

I will try to clarify my project for you.

1) I have inserted notes in my fields to clarify their purposes.

2) I intended to use these tables (tblProduct, tblTest, tblTestType) as lookup tables to construct the test plan for each product

3)This is the area I am having the most trouble with. My intentions are that the user will log in a sample (batch). The analyses required by the logged in product will appear (each test, test type,specifications, with a field for the user to enter the result for each test). These fields will be stored in a table. I would like the specifications at the time of the analysis to be stored in the results table. Thus, if the specifications change in the future, the specs that existed at the time of analysis will be preserved (the test type should also be handled this way).

4)Yes. To clarify the terminology, a 'Sample' is an Individual batch. A 'Test' is a single analysis performed on the sample. My intentions with the junction table was that the set of tests required by a particular product would be stored in the junction table. One product may only require 3 tests, while another may require 20 tests. I will call a set of required test for a particular product, a test plan. My intentions was that the individual batch would be stored in the sample table while the results for that batch would be stored in the sampletest table (a record for each analysis result for the batch).

5) The test type is the version of the test to be used. For example, we run pH on most of our products. However, we have many different versions of the pH test. So one product may require a pH (neat) while another may require a pH (5% aqueous). Many of our tests have several variants. Since the test plan is set up in the junction table I included the test type here.

6)I made some alterations to the database to help clarify it. I have also included two word docs of a test plan sheet that we use. The technician will fill in this sheet while running the tests for an individual batch. This results on the completed sheet will then be logged into the database.

To summarize the process I envision.

*The test plan for each product will preexist in the database

*A sample of a new batch of product will be analyzed by a technician and a physical sheet will be filled out

*The technician will log this batch into the database (product, date, batch#, tech name, retain#)

*The test plan for the logged in product will then appear and the value for each test will be entered in for that batch.

*I still am unsure as to how the specification data will be stored. This is why I have both quantitative high and low specs along with a text based qualitative field. For qualitative tests such as appearance I planned to pull results from a lookup table (there are over 100 possible results for appearance).

*I am also considering storing the quantitative data as text as well. It would be nice however to be able to perform calculations on quantitative results. It would also be nice to be able to flag any tests that do not meet the spec range. This could be alleviated by including a pass/fail field for each test, however.

I hope this helps clarify my project. I greatly appreciate you taking the time to help me with this. I have been studying my access books to learn as much as I can.

 

[plog]

That helps a lot. Let me make sure I understand:

tblProductTestJunct isn't the tests themselves, but a list of all tests that need to be run on a product sample.

tblSampleTest will hold the results of all those tests for individual samples.

If that's correct, I would suggest we start to think and work on this database like it has those 2 halves: TestSuite & TestResults. Overall, your database is going to be a little circular (because of tblProducts), but we can effectively eliminate that in our design.

TestSuite would be the part of the database used for keeping track of what products get what tests (basically tblProductTestJunct).

TestResults will deal with the samples themselves and the actual results of all their tests.

I have attached a crude image of the relationships that this database should have. I think a few tables need to be tweaked (tbSampleTest & tblTest).

Let me know if that makes sense and then we can start making that relationship work.

 

[funk88]

Plog,

You are correct the tblProductTestJunct will create the test plan (all of the tests required for each product) and tblSampleTest will hold the result information.

Dividing the database into two parts makes a lot of sense and looking at it in this way seems to simplify things.

How do you suggest I proceed with this? I really appreciate your insight.

 

[plog]

I would work on the TestSuite first and make sure it accomodates all your data. I would really make sure that each field is in the right table. Here's the questions I have:

TestType, TestCat: I really think these should be in (or linked to) tblTest, not tblProductTestJunct. Maybe they are poorly named and actually belong where they are now, but a category of the test and a type of the test just seem like they should be directly related to a test. As an example, you have a ProductCat and ProductType and those are in the Product table--I think this is correct.

I'm also leery of all the other fields in tblProductTestJunct. Are those actually related to a Product/Test combination or are they actually related to just the Test? Again, I don't know your data as well as you do, but it seems that some of that data would remain static no matter the product, which means its related to the test and should be in that table instead of tblProductTestJunct.

ProductType/ProductCat. I don't think you need both of these in Products. Normally, a Type has a set of Categories (1 to many). With your set up you have a many-many relationship, essentially Product Category and Product Type have no relationship. Is that true? If not, then you need a table (similar to tblTestType) that sorts what categorys belong to which types (or vice versa).

Those are the things I'd hammer out, then post a new relationship screenshot.

 

[funk88]

The TestType is unique to the analysis. For example, consider these
product - test -test type combinations:

Product A - pH - neat pH
Product B - pH - 5% aqueous pH
Product C - pH - 30% IPA pH

Each product has the pH test but the method that the pH is analyzed is different in for each product.

However, I do see how moving the test type to the test table will make more sense. I will do that.

The other fields in the junction table seem to belong there to me:

* Each product test combination will have a specification range.

* I could combine the LoSpec, HiSpec, and QUalSpec fields into one field named Spec.

*The Spec TestActive is there so that I can disable a spec if a test is no longer needed for that product (Perhaps this is not needed if the test info is saved in the sample test tables).

*The test sort provides a way for me to change the sort order of the tests for each product individually.

I will rework my tables and repost later tonight.

 

[plog]

* Each product test combination will have a specification range...* I could combine the LoSpec, HiSpec, and QUalSpec fields into one field named Spec...*The Spec TestActive is there so that I can disable a spec if a test is no longer needed for that produc

Those fields should remain seperate and in tblProductTestJunct. I would look into combining tblTest/tblTestType if you can. tblProductTestJunct should only be linked to one test table, even if that is a junction table itself which junctions tblTest to tblTestType

 

[funk88]

OK... I did some restructuring of my tables.

I moved the TestType data to the Test table.

I still need to create a lookup table for a list of possible qualitative specs. However in an earlier post you stated:

Tables with 1 real field (autonumber primary keys aren't real data fields). You shouldn't have tables that act simply as lists. That means tblTestType and as it stands tblTest are unnecessary. Instead of storing their ID in external tables, you would simply store the text that it relates to.

I am not sure I understand what you were saying here. What is the best way for the user to select a QualSpec from a list of choices on a form?

Created a form to enter analysis plans. I need to figure out a way to modify this form so that i can select the product name once and then it will fill into all of the lines for each individual test. It would also be good to lock the hi and low spec txt boxes on the form if a qualitative test is selected and lock the qual spec combo box if a quantitative test is selected.

 

[GinaWhipp]

Hmm, I've done one of these, you are missing some tables. You're going to need...

tblTestMethods
tlkpProductTypes
tlkpProductCategories
tlkpTestTypes
tlkpUnitOfMeasures

There might e more needed but you need to get the above in with the proper relationships.


Also...
-Every Table needs a Primary Key, use Autonumber
-You store Foreign Keys in the Junction Tables not the Names

Not going to worry about the Forms until you get the Tables set up.

 

[plog]

I am not sure I understand what you were saying here. What is the best way for the user to select a QualSpec from a list of choices on a form?

In design view of tblProductTestJunct, click on QualSpec and then at the bottom click on the Lookup tab. Change the Display Control option to 'Combo Box' and then the Row Source Type to 'Value List'. Finally, in the Row Source you input a semi-colon seperated list of values that can be selected.

Down the list is an option called 'Limit' to list. Set that to 'Yes' to make only items in Row Source to be used, set it to 'No' if you want to allow users to type in their own values (not recommended).

I do not agree with Gina that those other tables are needed--I believe they would all be tables with one real field of data and should be handled as I laid out above. I do agree that forms are something to focus on later.

 

[Minty]

@Plog this is interesting - I almost always create a table for lookup lists and store the PK rather than text simply because it is so much easier to maintain, and I don't have to find and update the value lists sometimes on multiple forms.
The down side can be that displaying the text means a query join but this doesn't seem to have any impact on the systems I develop?

 

[plog]

@Minty, another neat trick is the recursive lookup. If at the time of setup the table is to be populated with data and all the values that you want to use will be present in that data, you can set the Row Source Type to a query, and then set the Row Source to a query that looks back into the field itself for its distinct values.

 

[Minty]

@Minty, another neat trick is the recursive lookup. If at the time of setup the table is to be populated with data and all the values that you want to use will be present in that data, you can set the Row Source Type to a query, and then set the Row Source to a query that looks back into the field itself for its distinct values.

I have used this technique to restrict Account number lookups to ones that are in the underlying data on the form - it's very handy - I just forget to utilise it more often

 

[GinaWhipp]

@Plog

Hmm, I guess we will agree to disagree...

Having done one of these Lab Analysis databases before I have seen them grow beyond just recording results. To that end, wouldn't you want to make sure you database is ready to handle that growth?

That said, the standard would that is you find yourself repeating data in a *main* table time for a look-up table and relate accordingly.