Steven
Please don't  double post.  The other duplicate in the Developer Forum was deleted.

In general you don't want to put the calculations in a form directly.  Best practice is to create a Query to perform your calculations and base the forms record source off of the Query.

From there you can use the different functions easier than in a form.

Take a look at Access Expert 11 and Aggregate Query

Calculated Fields

Sorry about the double post... a few hours after I posted the original, I thought perhaps I put it in the wrong forum (Access Developer instead of General Access), lesson learned.

Well, I purchased Access Expert 11 today and it was no help at all. In fact, at the 1:30 mark of Lesson 1, Richard specifically says that Statistical Analysis functions will not be covered... oh well.

Aggregate Queries, as I understand, perform calculations on a specific field in multiple records. What I'm trying to do is perform a function (AVG or STDEV) on multiple fields within ONE record. I guess what I'm looking for is the actual SYNTAX that is used to perform one of these functions within a field in a query. As opposed to writing the following:

Average: =([Diameter1]+[Diameter2]+[Diameter3]+[Diameter4]+[Diameter5])/5

How can I use the built-in AVG function to perform the calc? It's not a huge deal to do it this way with something like AVG, however more complex calcs like STDEV would be rough.

First create a column to sum up all your values.
mySum: ([Diameter1]+[Diameter2]+[Diameter3]+[Diameter4]+[Diameter5])

Then create another column to get the average.
myAverage: [mySum]/5

I can't think of another way to do this without programmatically looping through the fields.

Steven - Sorry for the poor  pointer to the Statistical Analysis functions.  I just now read the "STDEV, STDEVP, VAR, VARP - Covered in future class." notation

That being said that is still the class you would need to Aggregate the figures and use those functions with.

Sorry I can't be of much assistance with Stats - I did not do well in that course in college and besides that was mumble mumble years ago.

Thank you all for the time, but I've got to believe that someone here has some experience using the built-in functions in the Expression Builder. I'm sure if someone could show an example using any of those built-in functions for calculating multiple fields within the same table (not aggregate functions which use the same field across multiple records), I'd be able to use that knowledge for the other functions as well. Anyone?

If you want to use the built in query Avg/StDev functions, and you have multiple fields with values, like 5 diameter fields. I would make two queries, one to use a UNION query, join all 5 of those fields into one field(column), the second based on that query to use the stdev/avg functions.

Union Query but in this case your joining fields inside the same table presumably.

Searching THIS forum there is only one reference to StDev.  The one I linked above, stating that it would be covered in the future.  That course is probably 10 years old so I'm guessing that there was not a great deal of feed back from people needing that functionality.

Searching the Microsoft web site doesn't yield much better results.  Still, nearly everything I read talks about Aggregating multiple records not using the functions in a single record.

LINK 1

Using SQL"
LINK 2

So the only way I can advise you to get the functionality that you want is to use a Query and put the formulae necessary to come up with the desired results for that single record.

Yeah, I don't think anyone has asked about StDev in the past ten years.

Steven, the StDev function works across a single field in multiple records. So if you have N1, N2, N3, N4, N5 all as fields in the same record, you'll have to convert that over to N1 in 5 separate records.

As the guys mentioned you could do it with a UNION query, but that's about the only way I can think of without using a Recordset loop.

I'll add this to my list for a future TechHelp video.

I'm probably out of line throwing my hat in the ring here, but as a thought:
Is there a role here for organizing the data a bit differently. Is this really 1 object with 5 different diameters? The same tree measured five times, or the diameters of 5 different trees?
Based on what you're records are, does it make more sense to have ObjectT and DiameterT, and make ObjectID your foreign key? Then you could query all the diameters for object 1 and it would be in the same field?

I appreciate the thought, but yes... essentially multiple measurements of the same "tree." I've actually simplified the example to try and make my point. It's being used in an Engineering environment where dozens of pieces of equipment are verified on an annual basis. In this case, a testing sieve. Each sieve is measured exactly 40 times (10 openings along the X-axis, 10 openings along the Y-axis, 10 individual wire diameters in the x, and 10 in the y. It's always 40 measurements per piece, no more, no less. The measurements must meet certain criteria in terms of average size, min and max dimensions, and standard deviation amongst measurements. What I'm finding is that this is probably not the best fit for an Access database.

I've made progress by defining each step of the Std Deviation equation within it's own field. One field is the mean of all measurements. Then a series of fields that represent the difference between each measurement and the mean, squared. Another field that sums those values. Then another field that divides that value by the population (n) and takes the square root. It was along way around, but gets the job done. The only part I have left to figure out is determining the MAX value within those individual measurements within the record. If someone can figure that out, I owe you a Coke.

I think you could do this just fine with different table setups, make a table with like
Autonumber, IDField, Measurement, MeasurementNumber, MeasurementAxis

make a new record "entry for every measurement you take"
1, 1, 5cm, 1, Y
2, 1, 4.9cm, 2, Y
3, 1, 4.9cm, 3, Y
4, 1, 5cm, 4, Y
etc...
So you have 40 measurement entries for every part, then you can use the built in StDev across the measurement field for each part, for each axis if you want to.

In fact, that table could just be a supporting table, and only store measurement information, then link it to the main "parts" table by the IDField (whatever you're using to uniquely identify them)

The reasoning behind the way I built it was to be able to *quickly* enter the data for each sieve. 3-digit number, tab, 3-digit number, tab, 3-digit number, tab, etc. 40 times in a row (the user is taking measurements as they go). I understand that creating a new record for each measurement will allow me to better take advantage of the built-in functions, but the trade-off in ease of data-entry and speed is probably not worth it.

You can just default value everything except the measurement, set the tab orders and set tab cycle to go to new record, and then you're right back to being able to tab 3 digit number tab 3 digit number etc...