Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    8

    Unanswered: Query Showing Mean and SD

    Apologies in advance but I'm still a little over my head with Access.

    What I'm hoping to do is to query a table (well, technically, another query) and output the mean and standard deviation.

    But with a complication. The mean and SD need to be only for a specific value, but with all in the same query. Confused? Example:

    Name Amt Date
    Bill $100 1/01/2011
    Bob $200 1/01/2011
    Bill $150 1/02/2011
    Bob $800 1/02/2011
    Bill $125 1/03/2011
    Bob $200 1/03/2011

    What I want to be able to output from the query is in the form of:

    Name Amt Date Ave SD
    Bill 100 1/01/2011 125 25.00
    Bob 200 1/01/2011 400 346.41
    Bill 150 1/02/2011 125 25.00
    Bob 800 1/02/2011 400 346.41
    Bill 125 1/03/2011 125 25.00
    Bob 200 1/03/2011 400 346.41

    It doesn't actually matter which 'order' the query outputs in, if that's easier. If I need to create another interim query in order to get the output as above, then that's fine as well!

    Any assistance appreciated!

    Thanks
    sjacko

    ps ... if the tables are hard to read, they are shown in these images:
    Attached Thumbnails Attached Thumbnails from.PNG   result.PNG  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im pretty certain the mean, sd and other statistical functions are available. however not every statistical function is present, IIRC MODE isnt

    as with all these sort of functions the art is marshalling the data in the correct sequence. but that will not let you get to your desired output (ie item data and summation data on the same line. frankly I don't understand why you'd want to.

    but the first part is easy enough use the AVG function
    for the standard deviation use stdev

    use a group by name.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    It might also help to know that you can use Excel Functions in Access:

    Access 2000 (good for 2003 as well)

    How to call Excel functions from within Access 2000

    Access 2007 (Good for 2010, I believe)

    Using Excel 2007 Functions in Access 2007


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Oct 2011
    Posts
    8
    Thanks for the suggestions / ideas. Certainly didn't know about Excel functions being available.

    In the end I've used a pretty simple workaround. Firstly created a separate query to calculate Ave and SD for each (grouped) value in the list. Then just added included this query to the 'primary' one to append these values to the line items as required.

    Appreciate the assistance though!

Posting Permissions

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