Results 1 to 13 of 13

011805, 08:44 #1Registered User
 Join Date
 Jan 2005
 Location
 Netherlands
 Posts
 5
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!

011805, 09:18 #2Purveyor of Discontent
 Join Date
 Mar 2003
 Location
 The Bottom of The Barrel
 Posts
 6,102
Provided Answers: 1Null 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.

011805, 11:00 #3Jaded Developer
 Join Date
 Nov 2004
 Location
 out on a limb
 Posts
 13,692
Provided Answers: 59Sounds 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

011805, 11:22 #4Registered User
 Join Date
 Jan 2005
 Location
 Netherlands
 Posts
 5
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.

011805, 11:29 #5Purveyor of Discontent
 Join Date
 Mar 2003
 Location
 The Bottom of The Barrel
 Posts
 6,102
Provided Answers: 1Hmmm... 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?

011905, 02:15 #6Registered User
 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.

011905, 09:38 #7Purveyor of Discontent
 Join Date
 Mar 2003
 Location
 The Bottom of The Barrel
 Posts
 6,102
Provided Answers: 1Oh 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; 011905 at 09:40.

011905, 10:20 #8Registered User
 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)

011905, 10:47 #9Purveyor of Discontent
 Join Date
 Mar 2003
 Location
 The Bottom of The Barrel
 Posts
 6,102
Provided Answers: 1Originally Posted by miepie
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.

011905, 10:59 #10Registered User
 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.LSPT.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

012005, 02:15 #11Registered User
 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.

012005, 03:33 #12Registered User
 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

012005, 09:45 #13Purveyor of Discontent
 Join Date
 Mar 2003
 Location
 The Bottom of The Barrel
 Posts
 6,102
Provided Answers: 1Originally Posted by miepie
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