Another Average question (2002 SP3)

Topic

New Reply

Ok, I am trying to set up a database for my quality control testing lab. I test 4 pieces for peel strength and would like to enter these into a form and have it report the average and standard deviation. In my table, i have- lot number, peel 1, peel 2, peel 3, peel 4. I just auto form to make my data entry form and added the calculation box. I can get the calculation to produce a total or sum but can not get it to give me the average.

Any suggestions?

Reply | Quote

Replies

Will you always have exactly 4 measurements, or could there be missing values? And is it conceivable that you’ll ever want to increase the number of measurements? If so, it might be better to change the table structure – just use one “peel” field, and add a record for each measurement. You could then design a report based on the table, grouped by lot number. You can place text boxes in the group footer that use the Avg and StDev functions.

With the current setup, you’d need to create a code module with field aggregate functions. The free NeatCode sample database from Microsoft contains a module with these functions; you can import the module into your database. Download it from ACC97: Neatcd97.mdb Available in Download Center (it is in Access 97 format, but it will work in Access 2002 too).

Reply | Quote

Thanks Hans, There are definitely times where I just check 1 or 2. Standard Operating procedures calls for 4 though. There may a rare occasion where I would do more.

Reply | Quote

If you decide to go with a separate record for each measurement, you could split the table – use table for the lot number and additional information, and a linked table with only two fields: lot number and result. For data entry, create a main form based on the first table, and a continuous subform based on the second one, linked to the main form on lot number. When you enter a new measurement result in the subform, the lot number will be filled in automatically.

Reply | Quote

After your previous suggestion, that’s kind of what I had in mind just haven’t had time to visualize it.

Thanks again.

Reply | Quote