Results 1 to 7 of 7

091004, 15:35 #1Registered User
 Join Date
 Sep 2004
 Posts
 2
Unanswered: Calculating Standard Deviation and Mean
I'm fairly new to Access and fumbling my around it. I'm an archaeologist and trying to analyze data from a large historic site that we recently completed work on. During our initial work at the site, we divided the area into a series of 25 ft x 25 ft grids. We counted the artifacts in each grid on the site (dividing them up into a series of different categories and subcategories).
In looking at the raw data, there appears to be significant variation depending upon who was doing the counting.
I originally set up four tables in Access with one table for glass artifacts, one for metal artifacts, one for ceramic artifacts and a fourth for misc. artifacts (shell, carbon rods from batteries, etc.).
I have already conducted a query to provide counts per grid square for total ceramics, total glass, total metal, total of misc. artifacts and total for the grid.
I would like to calculate the mean counts and standard deviation for these variables per person (for person 1, what was the mean number of ceramics in each grid square they examined).
I have a table set up with each row corresponding to a specific grid square, and columns for person, ceramictotal, glasstotal, metaltotal, othertotal and gridtotal.
I have been trying to figure out how to calculate the standard deviation and mean, but have been completely unable to figure out how to word the query to conduct these calculations.
Any help would be greatly appreciated.
Thanks,
Jeff

091004, 16:28 #2Registered User
 Join Date
 Jul 2004
 Location
 Southampton, UK
 Posts
 368
Jeff,
There are some built in aggregate functions available. They are best used in either a form of report.
For standard sample (unbiased n1) deviation:
DStDev("[myDataColumn]","myTable",myCriteria)
For standard population (biased n) deviation
DStDevP("[myDataColumn]","myTable",myCriteria)
If you are not familiar with how to aggregate function then detail your table names and some criteria and we can talk you through it.
Chris

091004, 16:48 #3Registered User
 Join Date
 Jul 2004
 Location
 Southampton, UK
 Posts
 368
Sorry, forgot to mention Davg(...) gives the mean
Chris

091004, 16:57 #4Registered User
 Join Date
 Sep 2004
 Posts
 2
Thanks for the info. I think I understand what you are saying. I'll play around with it a bit and see if I have any luck.
Jeff

091004, 17:11 #5Registered User
 Join Date
 Jul 2004
 Location
 Southampton, UK
 Posts
 368
ok. if you get stuck...
create a blank report in design view (don't bother linking to a table or query)
add a text box
go into the properties for the text box
in the control source, enter your aggregate function:
=Dstdev(...)
note that I put an = sign at the front
you can add as many text boxes with different aggregate functions from different tables as you like.
Chris

091004, 18:36 #6Registered User
 Join Date
 Sep 2004
 Posts
 2
I think I need to be walked through this. I'm still not getting the logic behind the expressions.
I'm trying to use a query I created earlier entitled "personaverage" which has several column headings "northing" "easting" "person" "ceramicstotal" "glasstotal" "metaltotal" "othertotal" and "gridtotal".
Combined, the northing and easting columns create the key fields for each grid. I'm currently trying to determine the mean and sdev of ceramic counts by each person per grid (for example, if person 1 counted in 4 grids, and had ceramic totals of 12, 19, 22 and 13, then the mean for that person would be 16.5).
I've started out with the expression: =DAvg([personaverage]![ceramictotal], but from there, I'm not sure where to go.
Should I do a separate expression for each individual (iif[person]=1 ??
Thanks again,
Jeff

091004, 20:05 #7Registered User
 Join Date
 Jul 2004
 Location
 Southampton, UK
 Posts
 368
Jeff
Here's an example (attached). I created a table of archealogists (People) so that I could use this as a list to base a query on. Then I dropped in some aggregate functions which will aggregate against each person. I've then created a query and written the D functions in a way that cycles through each person and gives the aggregates. So you get a neat query with the date you need. The data is drawn from tblData which is roughly what you are using I think.
The point about aggregate functions is that they return exactly what you ask them to.
e.g. "tell me the average ceramicstotal from myTable where person is Jim"
You can stick the "D" functions pretty much anywhere and you will still get the same answer.
The problem with using queries to calculate StdDev from scratch is you need to first calc Mean (as you probably know) and use this as a fixed value. It's not impossible, it's just not straight forward (at least not to me on a Friday night after a few beers ).
HTH
Chris