Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2005
    Location
    Netherlands
    Posts
    5

    Question Unanswered: replacing numerical zero with Null

    Hi,
    Problem!!!
    In my Access database I want to calculate means and standard deviations of certain variables (for example gait velocity), but the computer automatically assigns the numerical value 0 to missing values and uses the 0 in the calculations. As Null is a string, I cannot displace 0 with Null.
    Who knows a way to replace 0 with something Access recognizes as 'missing value' (and don't use it in calculations)?
    Would be of great help!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Null isn't a string, it's simply "unknown".

    Could you give an example of an expression returning 0 that you want to change to null? I'm curious as to what calculations you'd want to use null in, as any calculation involving null will always return null.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sounds like you have set, or left, the default value of the column as 0 not NULL
    In terms of restting, run a quick query to reset to null where value =0

  4. #4
    Join Date
    Jan 2005
    Location
    Netherlands
    Posts
    5

    Unhappy

    Great that you're thinking with me!
    It's rather complicated but I'll try to explain the problem.

    I use an electronic walkway that can register footsteps, gait velocity and so on. Every person walks several times over it and the computer calculates these things and stores them in an ACCESS database.
    Example:
    To calculate the length of a step, the computer needs two points (=2 steps)on the walkway. For the first step it puts 0 in the database at the place of 'step length'. After that is's all right. But when I want to know the mean of steplengths of a walk, ACCESS also uses 0 and the mean is not correct.
    (not possible to do in SPSS as I convert for each patient several tables to one plain one)

    Simply replacing 0 with Null doesn't work.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hmmm... shouldn't step length be a calculated value as opposed to stored? It would seem easier to store the relative position of different steps instead of the step length... Whereas you're using 2 points in your calculation, you should (theoretically) be able to derive step length at run time...

    What does your calculation look like?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jan 2005
    Location
    Netherlands
    Posts
    5
    Actually, it works with coordinates just as you thought and the step lengths are calculated during run time. But the variation coefficient (mean divided by standard deviation) and standard deviation is not.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oh I see what you mean here...

    I would think you would want to throw the first score out entirely wouldn't you? When calculating mean, counting the "first" step is going to through your whole calculation off, as it's not really a step at all but a starting point. I would suggest leaving it out of your calculation. If you are asked to calculate the mean between 2, 4 and 7, would you use 0 as well because you need somewhere to start? I would imagine you want to start with the first meaningful value in the set, no?

    For instance, say you have these same 4 "steps":

    0, 2, 4, 7

    The first step is 0 because that's the starting point. If you include it in a mean calculation you get:

    3.25

    However, the first step wasn't really a step at all. If you discount it, you get:

    4.33

    Whereas standard deviation is calculated using difference between deviation and mean, squared, for each value, you're going to see the same inconsistency.

    Granted, the more steps you include in the calculation, the less impact this will have. However, it's there.
    Last edited by Teddy; 01-19-05 at 10:40.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jan 2005
    Location
    Netherlands
    Posts
    5
    You've got it!
    Now how to find a way around...
    I'll get there. At least I hope.
    (maximum number of steps is around 20 so the first 0 still matters)

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by miepie
    You've got it!
    Now how to find a way around...
    I'll get there. At least I hope.
    (maximum number of steps is around 20 so the first 0 still matters)
    That doesn't make sense. Why do you need the first 0 to calculate the number of significant hops?

    I'm sorry but your logic is broken. You can't count insiginficant values. By definition of mean and standard deviation, you will hose your data.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Dec 2004
    Posts
    20
    Hi Miepie!

    Maybe this helps you:

    You should have a table yourTblName (composite primary key made of personID (= p1), stepPoint (= p2) ) where you would store all step points including the starting point 0. Then you would just have to make a query like this:
    SELECT T.p1, T.p2 As FirstStepPoint, PU.LSP As LastStepPoint, (PU.LSP-T.p2) AS STEPVALUE
    FROM yourTblName T
    INNER JOIN
    (SELECT Min(T2.p2) As LSP, T1.p1 AS K1, T1.p2 AS K2
    FROM yourTblName T1
    INNER JOIN
    yourTblName T2
    ON T2.p1=T1.p1 AND T1.p2<T2.p2
    GROUP BY T1.p1, T1.p2
    ) As PU
    ON T.p1=PU.K1 AND T.p2=PU.K2;

    Then you can make a form based on this query and calculate mean value and standard deviation.

    Regards!
    BoHr

  11. #11
    Join Date
    Jan 2005
    Location
    Netherlands
    Posts
    5
    Thanks BoHR, I'm going to try your suggestion.
    Teddy: As 20 is not that much, I don't want to use the first 0 but I know no way not to use it in the calculations.

  12. #12
    Join Date
    Dec 2004
    Posts
    20
    Hi Miepie!

    You're welcome!

    However, though my query works properly you don't have to calculate STEPVALUE immediately but on your form's control (as [STEPVALUE] = [LastStepPoint]
    - [FirstStepPoint] ) and then you would have even simpler query:

    SELECT T1.p1 AS K1, T1.p2 AS FirstStepPoint, Min(T2.p2) As LastStepPoint
    FROM yourTableName T1
    INNER JOIN
    yourTableName T2
    ON T2.p1=T1.p1 AND T1.p2<T2.p2
    GROUP BY T1.p1, T1.p2


    If you want to get rid of 0 and store step points without the starting point 0 then use this slightly more complicated query:

    SELECT T1.p1, 0 AS FirstStepPoint, Min(T1.p2) As LastStepPoint
    FROM yourTableName T1
    GROUP BY T1.p1
    UNION ALL
    SELECT T1.p1, T1.p2, Min(T2.p2)
    FROM yourTableName T1
    INNER JOIN
    yourTableName T2
    ON T2.p1=T1.p1 AND T1.p2<T2.p2
    GROUP BY T1.p1, T1.p2

    Regards!
    BoHr

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by miepie
    Thanks BoHR, I'm going to try your suggestion.
    Teddy: As 20 is not that much, I don't want to use the first 0 but I know no way not to use it in the calculations.
    Leave it out of the dataset by paring down your criteria.

    Only pull steps with a distance > 0

    SELECT *
    FROM yourTable
    WHERE [step distance] > 0

    I'm assuming your doing your calculations at the report level? If you're not.. you probably should
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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