Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Red face 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

  2. #2
    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 n-1) 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

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Sorry, forgot to mention Davg(...) gives the mean

    Chris

  4. #4
    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

  5. #5
    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

  6. #6
    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

  7. #7
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •