Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Unanswered: Query math: z score

    Hey everyone,
    I'm designing an update query to calculate the z score of a set of values. I seem to be having trouble getting the format for the calcuation though. Here's the basics:

    Table1 has a column called Test with 5 results. Table2 has a column called zTest with five spaces. Table2 is where the calculated value needs to go. The calculation that I need done for each test is:

    (Test-Average(Test))/StandardDeviation(Test)

    If access already has this function and it's called something else, then I apologize for not knowing it. At any rate, I just need assistance in setting this up in the Update To of the query. If this needs to be done another way, please let me know. Thanks for any help.

    David
    Last edited by geckoinc; 12-15-04 at 09:16.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Try looking at the DAvg() and DStDev() (or DStDevP()) domain function in Access help.

    I think this is what you need.

    MTB

  3. #3
    Join Date
    Jul 2004
    Posts
    34
    That's part of my problem. The help info is anything but helpful. Feels to me like it's missing some info. Anyway, what I mostly need help with is the format to put in the Update To box of an Update query (if this can be done in an update query). If it acted the same way as excel, this would be easy, but it doesn't appear to work the same way. Any help on the format would be great.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    One think at a time.

    Can you return the required (correct) result of the calculation ?

    ie. use a temporary button Click to do say,

    Code:
    Sub Button_Click
    Dim Answer as Single
    	
          Answer = (Test-DAvg(“Test”,”Table1”))/DStDev(“Test”,”Table1”)
          Msgbox Answer
    
    End Sub
    Just to understand the function operation.

    I have assumed you want to calculate with all 5 values in the Test field.


    If you can get this fare then try pasting ‘ (Test-DAvg(“Test”,”Table1”))/DStDev(“Test”,”Table1”)’ into the Update To field.

    If that doesn’t work you could always define an SQL Update query in code and use the DoCmd to do it?

    Just some ideas, as I have not used domain function in Update To fields. I have used DLookUp etc with SQL in code with update queries and DoCmd though.
    MTB

  5. #5
    Join Date
    Jul 2004
    Posts
    34
    Thanks Mike for trying to help this lost soul. However, the first code did not work. I believe it's because of the format. I'm still learning Visual Basic here, so the format (all the brackets, wording, orders, and such) still trips me.

    To clarify what's happening here, there are going to be several test values (I said 5 just to be easy, but it's more like 60). Each test value needs a z score calculated to show the amount it is away from the average of all the tests. All these calculated zScores go into their own table and then get displayed in a report where further calculations are done (reason that they need to be calculated ahead of time).

    I thought an update query would solve the problem except I do not know how to format the formula. I've tried searching for info on doing complex formulas based on another table, but have not been successful in finding anything.

    Just to make sure that my formula makes sense, here's a bit more in depth:

    zScore = (Test-Avg(Test))/StDev(Test)
    Where Test is the individual test.
    The Avg(Test) is the average of all the tests listed.
    The StDev(is the standard deviation of all the tests listed.

    I'm running Access XP if that makes a difference.

    Again, thanks for helping me through this. Hopefully the answer is a simple one

    David

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    zScore

    Hi geckoinc

    I think we need to go back even further and ask why you need to save these values when they can be can calculated as and when required with a query (see attached DB). This could be used as the data source for forms/reports.

    This is even more important as the values will change when you add records to Table1 (assuming that Averge and/or Standard Deviation are based on the whole table).

    If you want to save the values at a given moment in time in Table2 then we need to know the relationship with Table1 and the Primary Key(s) and Foreign Key(s) in Table2

    Hope attached is of help with you problem


    MTB
    Attached Files Attached Files

  7. #7
    Join Date
    Jul 2004
    Posts
    34
    Thanks for the help. Hopefully I can get it from here with the example. It gives me a good starting place.

    To answer your question from before, the value needs to be stored because I have a report that calculates other values from it, and I thought I remember reading that it won't calculate other calculations. Also, this query would only be run once when the deadline for tests is up, so there's no worry of changes afterward (because they're too late). We'll see how it goes. If I need to take a different course of action, then I will. Thanks again!

    David

  8. #8
    Join Date
    Jul 2004
    Posts
    34
    Thanks for the help. Hopefully I can get it from here with the example. It gives me a good starting place.

    To answer your question from before, the value needs to be stored because I have a report that calculates other values from it, and I thought I remember reading that it won't calculate other calculations. Also, this query would only be run once when the deadline for tests is up, so there's no worry of changes afterward (because they're too late). We'll see how it goes. If I need to take a different course of action, then I will. Thanks again!

    David

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    I still do not see why you need to save the calcs as you can use the calculated vales in further calculation (see moded query attached), although subsequent calcs could be quicker if it is a very large record set.

    If you want to store the cals I think you coud save them in the same table, as each zTest only relates to a single Test value (?). In which case you could run the udate query in the attached DB.

    All the best.

    MTB
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Thanks for your note geckoinc, but one last thought.

    As it stands at the moment the calculation will use ALL the Test values in the table, even those with a zTest value. If that is not what you want then I have modified the queries to use and update only the Test values that do not already have a zTest vlaue (zTest = Null).

    The mods are in the attached if that is your requirement?


    MTB
    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
  •