Form Design issue

[ramez75]

Hi,

I am working on adding to an existing database a "Test Form" that will be used to input product test data collected during product trials / validations replacing the current excel sheets. The reason I want to do that is I can build queries and store all data in one or by most 2 tables instead of 100'ds of excel worksheets. Also the only calculation involved will be Averages and Standard deviation.

The way I think i can do this is a frmTest (linked to tblTestForm) and a frmTestSubform (linked tblTestSubform) for each of the test method and I use a Switchboard form to access each form. Also frmTestForm gets info using combobox control from to other tables (tblProductinfo) and (tblTestName). Ofcourse the different forms will be using the same table/tables but certain fields of the table/tables

One of my issues is since I have different tests for a product, I wanted to try to use one form that can be tailored by the USER instead of having one form for each test method (attempt failed so far), that way I will have one table or two at most then I can run a query to manipulate the data. I would like the form to show averages and STDEV for certain attributes when the user is inputing the data so they are aware if product is passing or not. It gets better certain attributes need be calculated first.

How and which is the best way to go about it.

For example...
Product XYZ (consist of 2 items) when validated need to be tested for
1. TotalWeight
2. Strength
3. Capacity
Hence, the test forms should be setup differently. The Header of the forms on the other hand will be the same regardless which test method is used and consist of for example Machine speed, Product Name (ComboBox), Date Tested, Tested By, Trial/Validation Number, Glue Amount (lets say frmTest).

TotalWeight (lets say frmTestSubformW) - Will consist of 4 columns, 3 columns use inputed data by the tester and last column will be calculated
Column 0 - Sample Number
Column 1 - Weight1
Column 2 - Weight2
Column 3 - ([Weight2]+[Weight1])
Also while the Tester is keyin in the data, I would like frmTest to show the average and standard deviation of lets say the samples tested.

Strength (lets say frmTestSubformS) - Will consist of 3 columns, 2 columns use inputed data by the tester and last column will be calculated
Column 0 - Sample Number
Column 1 - ProductWeight
Column 2 - Equation goes here
Also while the Tester is keyin in the data, I would like frmTest to show the average and standard deviation of lets say the samples tested.

Capacity (lets say frmTestSubformC) - Will consist of 6 columns, 4 columns use inputed data by the tester and last 2 columns will be calculated
Column 0 - Sample Number
Column 1 - ProductWeight
Column 2 - Filter1Wt
Column 3 - Filter2Wt
Column 4 - ([Filter2Wt]-[Filter1Wt])
Column 5 - ([ProductWeight]-[Filter2Wt])*100
Also while the Tester is keyin in the data, I would like frmTest to show the average and standard deviation of lets say the samples tested.

Again all the above are made up to just give an idea of what I have in mind.

Any ideas/help is appreciated

Thank you

 

[Mr. B]

ramez75,

You mention that you think that one or maybe two tables may be needed. I really think you need to rethink you database design.

Just taking your first description of what you are wanting to do:

For example...
Product XYZ (consist of 2 items) when validated need to be tested for
1. TotalWeight
2. Strength
3. Capacity

It is clear from this description that you will need a Products table, an Items table and a Test table. If the results of the test cannot or should not be stored in the Test table then you would also need a Results table.

And that is just for starters. The design of your database is the most critical thing you can do as you start any project.

 

[ramez75]

Mr.B....yes I actually have 4 tables.

The way I think i can do this is a frmTest (linked to tblTestForm) and a frmTestSubform (linked tblTestSubform) for each of the test method and I use a Switchboard form to access each form. Also frmTestForm gets info using combobox control from to other tables (tblProductinfo) and (tblTestName). Ofcourse the different forms will be using the same table/tables but certain fields of the table/tables

tblProductinfo - Product Table
tblTestform - will be for the common items in the form such as Test Date, Tested By, Machine speed, Product Name, Validation/Trial #, etc....common items between all forms regardless of the test.

tbltestSubform - This is the table that will hold all the results from which I will extract the data for reports.

 

[ramez75]

This what I have so far....still cant get it to work as intended, I think having problems with the relationships and primary key.

I created 4 tables
1.tblTestName (contains 11 different tests) made up of 1 column linked to frmtest. No primary Key
2.tblProductinfo (contains about 150 products) linked to frmtest. ProductID Primary KEy
3.tbltest (contains the form header; 11 fields) linked to frmtest. ProtocolID primary key
4.tbltestdetails (contains the data for the necessary test chosen) linked to frmtestdetail and used in frmtest as a Subform. ProductID and ProtocolID are primary keys

I open frmtest and fill out the header and in the i can only fill one row in the Sunform (frmtestdetail) and when I close the form and check the tables there is no data saved in tbltestdetails

Is the approach wrong or is there a better way

 

[Mr. B]

You do not mention anything about Foreign Key fields being in any of your tables. Primary keys identify individual records in any table. Foreign key fields allow the records form one table to be linked to the records in another table by the fact that both tables have the same unique values.

For example, I am assuming because of your original description of your project that your data primarially starts with a Product and that that product may have multiple items that need to be tested. If this is the case then you would have your "tblProductinfo" table that has a primary key.

Then you would have your "tbltest" table which, in this case, would have the items to be tested. In this table you would have a primary key field, any other fields required to identify each item to be tested and a foreign key field. The foreign key field is a number type field defined as a long integer type. It will have the value from the Primary Key field of the record in the "tblProductinfo" table to which the Test is related. You would then have your "tblTestName" table with a primary key and a foreign key field to link to the "tbltest" table and so on.

I hope you are beginning to ge the idea.

I would suggest that you search for and read up on table design in a relational databse. Also look further into the user of Primary Keys and Foreign Key fields.

 

[ramez75]

This what I have been able to come up with. I have attached the sample database. The thing i cant get to work still is the Sample No, I would like it to be typed in and not predefined. I also would like to be able to show the Average of the calculated weight but i get an error when I do so

 

[Mr. B]

Can you give me a little more information about just what you are wanting to do with the Sample number? Exactly what is it for?

 

[ramez75]

The sampleNo, is for tracking how many sample products had been tested by each test.
Example

Test1 lets I needed to test 30 sample products per batch size

Test2 i need to test 15 sample products and so on......So with time i might have 100ds of "1" as sampleNo and "2" etccc

 

[Mr. B]

Let's see if we can start from the beginning. This is getting confusing to me. Let's see just how confused I am.

You have products that are submitted for testing.
Each product can have multiple items that need to be tested.
Each Item can have multiple samples taken from it for testing
For each item that is to be tested you will have multiple tests that must be performed.

Ok, now what did I get wrong?

 

[ramez75]

Let's see if we can start from the beginning. This is getting confusing to me. Let's see just how confused I am.

You have products that are submitted for testing.
Each product can have multiple items that need to be tested.
Each Item can have multiple samples taken from it for testing
For each item that is to be tested you will have multiple tests that must be performed.

Ok, now what did I get wrong?

Apologize for the confusion that i caused.

You got most of it right but there is no "items" the product is the item.

1. Products are tested by different test methods (Test1, Test2, etc).
2. Product tested by "Test1" (for example) cannot be tested by other test methods (NOTE: All test methods are destructive to the product, hence i will need different "SampleNo" for each test method required during the Protocol/Trial, i cannot use for sake of example 20 product tested by Test1 in Test2, Test3, etcc)
3. Products are sampled depending on the test methods used. Sometime could test 20 products or 30 or 10, etc depending on the sampling plan established during the Protocol / Trial
4. All the above will be triggered by the Protocol No / Trial No, unless a Protocol/Trial is initiated or planned the TestForms will not be used.

Please let me know if that make sense or if I caused more confusion

 

[Mr. B]

I have modified your tables slightly and changed the relationships. Take a look and you will see how they now related to each other. This should get you going.

 

[ramez75]

Ok I think I got the form to work as needed. The only thing I would like to do is for the Header field to update automatically as the USER adds new row in the subform. I have attached an image for clarification.

Example:

User adds Sample No. 7 (as shown in attached picture) as soon as Calculated Weight field is populated or USER moves cursor to next row, I want all the header fields to update (Average Of Strength1, Average Of Calculated Weight, etcccc). Right now I have to close the form and open it back up. Is there something I can use like [Event Procedure]. I just cant figure out how to go about it

 

[Mr. B]

Try using the AfterUpdate event of your sub form and add this code:

Forms!NameOfYourMainForm.Requery

Just add the actual name of your main form to the code above.