Help with calculations (1 Viewer)

[QAGuy]

Ok I'm really new at using access so go easy on me. I am trying to write a database to keep 30 measurements of a part grouped by lot number and then calculate the standard deviation, mean and a few other calculations, however I am unsure about how to do the calculations in access.

I would like the database operator to be able to enter the data then print out a report per lot, with the entries and the calculated answers.

I'm just unsure where and how to do the calculations... any help or suggestions?

Thanks in advance!

 

[WayneRyan]

QA,

That's a very general question ... probably why you haven't got any responses.

If you structure your data properly (see normalization; parent/child relationships)
then SQL will easily provide you with the stats you need.

Need more information,
Wayne

 

[Mike375]

If you go to Access Help on the toolbar and search on Calculations you will be on your way.

 

[ajetrumpet]

If you go to Access Help on the toolbar and search on Calculations you will be on your way.

He should be on his way without any help at all Mike. He's in the QA business...

 

[QAGuy]

Ok so here is my real problem. I have a table set up with each record being a specific product lot number. Across the 30 fields I have measurments. The built in StDev function in expression builder wants to look at one field only. Is there a way to calculate the StDev across the 30 fields instead?

 

[ajetrumpet]

Ok so here is my real problem. I have a table set up with each record being a specific product lot number. Across the 30 fields I have measurments. The built in StDev function in expression builder wants to look at one field only. Is there a way to calculate the StDev across the 30 fields instead?

Upon initial thought of this, I'm not sure you can do this stuff sideways, as I've had these conversations before. But, I'll tell you, I would do this using a dynamic array to get the rows, and then somehow create an StDev extraction of the values in vertical sequence. I know this is possible because I've already worked with the array inputs and outputs in this manner. Unfortunately, I also know that it is a complicated process to go through, but I'm not sure if you have any other choice here (unless of course you can somehow get your data in a "permanently vertical format without writing the transpose function)...

 

[Mike375]

Adam,

I don't have the maths for this. Is standard deviation the distance (number) either side of the average?

Given it he has the number of fields then it is easy to do the average across each row. Can standard deviation be done.

Depending how many records there are it would be easy to make a little macro or code that would get the "across the rows" into field in another table but that would be limited to doing 255 records form the table in question. Actually you could have several tables made and with a counter the macro or code would switch to another form/table for each run of say 250. This would be good to watch at night time with Echo Yes

 

[WayneRyan]

QA,

If your table(s) are normalized ... with NO repeating fields, then SQL can
handle this quite easily. Remarkably easily.

If your table structure has a set of 30 repeating columns, then you'll have
to expend a lot of effort to extract your stats. I mean a lot of effort.

It's attainable, but I'd change your data structure first. Much easier in
the long run.

Wayne

 

[ajetrumpet]

It's attainable, but I'd change your data structure first. Much easier in
the long run.

Amen to that Wayne!

 

[ajetrumpet]

I don't have the maths for this. Is standard deviation the distance (number) either side of the average?

Not really. It's figured in a really strange way, like this:

Test Set = [3, 6, 9, 15, 28, 20, 5]

Average = 86 / 7 = 12.3
Deviations (from the mean) = [-9.3, -6.3, -3.3, 2.7, 15.7, 7.7, -7.3]
Deviations Squared = [86.49, 39.69, etc...]
Variance (Ave. of Squared Dev's) = 71.9
Std. Dev (Square Root of Variance) = 8.48

That's the correct process. Now, do it the common sense way, and average the actual deviations of each number from the mean of the set. You get this:
(-9.3, + -6.3, + -3.3, + 2.7, + 15.7, + 7.7, + -7.3) / 7 (numbers) = -.014

No why don't those numbers match up I wonder...

Common sense doesn't come into play here....sorry!

 

[Mike375]

If you drop the minuses (which squaring does) then it 7.47 and that would be the commonsense way.

 

[WayneRyan]

QA, Adam, Mike:

If you normalize the data, there are built-in functions:

Select tblEmployee.Name, 
       Stdev(tblTimeCards.hours_worked),
       Max(tblTimeCards.OvertimeHours)
From   tblEmployee, tblTimeCards
Group By tblEmployee.EmployeeID
Where  tblEmployee.EmployeeID = tblTimeCards.EmployeeID

The domain functions like Max, Avg, Stdev can be applied to all rows
very easily, even 30 distinct columns. <-- "just not horizontally"

But if you need to compute something horizontally, then you'll be
implementing the formulas in code and in general having a miserable
time.

If you had to keep the current design, your best approach would be to
use ADO or DAO recordsets and loop through the fields collection:

'
' Process each row ...
'
Total = 0
For i = 0 to rst.Fields.Count -1
   Total = Total + rst.Fields(i)
   Next i
'
' Then comput the Average, Mean, Std Dev. or whatever for the row.
'

Personally, I really vote for the SQL approach.

hth,
Wayne

 

[ajetrumpet]

If you drop the minuses (which squaring does) then it 7.47 and that would be the commonsense way.

That's very profound Mike. That's shifting data around if I've ever seen it before...

QA, Adam, Mike:

If you normalize the data, there are built-in functions:

No kidding.... Sorry Wayne, but we've kinda veered off the path of normalization for a while. Who cares...

Why don't you put together a nice little example for us, eh??

 

[Mike375]

The domain functions like Max, Avg, Stdev can be applied to all rows
very easily, even 30 distinct columns. <-- "just not horizontally"

Wayne,

Unless I am reading QA incorrectly he could finish with a lot more than 30 columns. If had a 1000 records then he would have a 1000 columns. If the data was arranged in one column and he originally had 1000 records then a single column would have 30000 records and Access would need to separate that into batchs of 30 records to do the sums.

Provided Access is OK with 32 calculated fields maybe the easiest/fasted way is:

First CalcField gives the average across each row.
CalcFields2 to 31 do each table field less the average and square
CalcField does the average across CalcField2 to CalcField31

I have one query I just checkec that has 28 calculated fields. Mainly all Left/Right/Mid and IIF and Len and calculating of each other.

 

[WayneRyan]

QA, Mike & Adam,

This is all I can do for a sample, assume that you want to track parts per lot with
any number of different metrics associated with each part.


tblParts
========
PartID - AutoNumber
PartName - String

tblLots
=======
LotID - AutoNumber
PartID - FK to tblParts
LotName - String

tblMeasurementTypes
===================
MeasurementTypeID - AutoNumber
PartID - FK to tblParts
LotID - Fk to tblLots
MeasurementType - Any number of types: height, volume, weight, whatever


tblMeasurements
===============
MeasureMentID - AutoNumber
PartID - FK to tblParts
LotID - Fk to tblLots
MeasurementType - FK to tblMeasurementType
Measurement - One of the 30 samples (or one of a thousand samples, it doesn't matter)


Then you add the type measurement to give you the Standard Deviation for the
Height, width, Volume or however many types you have.

Select A.PartName,
       B.LotName,
       C.MeasureMentType,
       Stdev(D.Measurement)
From   tblParts As A Inner Join tblLots As B On
         A.PartID = B.PartID Inner Join tblMeasurementType As C On
           A.PartID = C.PartID And
           B.LotID = C.tblMeasurementTypeID Inner Join tblMeasurements On
             A.PartID = D.PartID And
             B.LotID = D.Lotid And
             C.MeasurementTypeID = D.MeasurementTypeID
Group By A.PartName, A.LotName, MeasurementType

Output:

=====   ====   ======   ====
Bolts   Lot7   Height   34.2
               Weight   47.5
               Volume   22.4

Bolts   Lot8   Height   34.2
               Weight   47.5
               Volume   22.4

btw, I'll let SQL do the Stdev calculation. I seem to recall that Stat classes were
not really that much fun.

hth,
Wayne

 

[Mike375]

I tried this with about 5000 records and it worked OK, query opened straight away. I think I did the calculation correctly.

This was quick to make, about 20 minutes but obviously I used field names to help and so a bit longer with actual field names.

I just realised I forgot to do the square root at the end