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.
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 ??
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 ).