How do I handle numeric data with < values in Access?

G

Guest

I use Access2000 to handle large amounts of chemistry data. Some chemical
parameters can have values less than a detection limit (ie <0.01). I still
need to present this as a real number in calculations (0.01). However, to
present this information accurately in a technical report where we created
Access graphs and tables, we have had to resort to 2 columns (numeric and
text) for every one parameter that could potentially have a "<" value so that
depending on our presentation needs, we present it numerically (-0.01, where
the '-' sign is a place holder for the '<' ) or, in a text form as it really
should be(<0.01). The '-' sign was suggested by a database developer years
ago when we started using Access in Win95 as a way to still visually show '<'
values in the numeric field. This is the problem. Mathematically when
performing calculations, a '-' means something different than '<'.

The result is a database that has twice the number of columns than what is
really needed, inaccuracies in calculations of a negative number, and
rehandling of data resulting in human error (ie when users of the data export
to Excel, changing the numbers manually).

So, does anyone know if there is a way yet in newer versions of Access that
data with "<" values can be handled and still used numerically without using
the '-' ?
 
R

Rick Brandt

TJ said:
I use Access2000 to handle large amounts of chemistry data. Some
chemical parameters can have values less than a detection limit (ie
<0.01). I still need to present this as a real number in calculations
(0.01). However, to present this information accurately in a
technical report where we created Access graphs and tables, we have
had to resort to 2 columns (numeric and text) for every one parameter
that could potentially have a "<" value so that depending on our
presentation needs, we present it numerically (-0.01, where the '-'
sign is a place holder for the '<' ) or, in a text form as it really
should be(<0.01). The '-' sign was suggested by a database developer
years ago when we started using Access in Win95 as a way to still
visually show '<' values in the numeric field. This is the problem.
Mathematically when performing calculations, a '-' means something
different than '<'.

The result is a database that has twice the number of columns than
what is really needed, inaccuracies in calculations of a negative
number, and rehandling of data resulting in human error (ie when
users of the data export to Excel, changing the numbers manually).

So, does anyone know if there is a way yet in newer versions of
Access that data with "<" values can be handled and still used
numerically without using the '-' ?

Just eliminate all of the "-" except where it is actually a negative number.
If you want the value of <0.01 to be treated as 0.01 for the purposes of
calculation then just enter 0.01. Have an additional Text column that you
enter "<" into if the value is really intended to be <0.01 instead of a
literal 0.01.
 
R

Roger Carlson

The problem is <0.01 is NOT a number. It is a numerical concept. You might
want to store NULL if the value is <0.01. NULL means the value is
"unknown", which is how you are using <0.01. Now if you store NULL, it will
ignore this for calculations. But in your report, you can replace NULL with
a different value for display purposes using the Nz function. Something
like:

Nz([Field1],"<0.01")

This will display <0.01 but will not use it in a calculation.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
G

Guest

This becomes a bit of a philosophical problem. Access deals with discreet
numbers or with text. To put a "<" sign in with a number is not possible,
because that is no longer a discreet number, it has become text. However,
putting a negative sign in front of a number makes it a negative number, and
changes the meaning of the number. It is not possible to have one field give
you all the information you need, and it is making a lot of work to maintain
two or more fields just because you need a discreet number sometimes, and
other times need to know that a number is actually statistically
insignificant.

In a case like this, it is best to let the database itself do the work of
deciding whether a number is significant or not. Best case is, that your
detection limits are unique to each chemical parameter. Such as, sodium
always has a detection limit of 0.01, or chlorine always has a detection
limit of 0.10. In these cases, you can set up a table that has those
detection limits for those particular chemicals. Then, when creating a query
that will become a report, you can compare the actual value to the detection
limit, and have a text field that will go into the report, which will either
have the actual value, or print <0.10, or even print NDA (no detectable
amounts), depending on whom the report goes to. It would all be handled
through a carefully crafted query.

Now, when you move your data over to an Excel file, the actual numbers exist
as actual positive numbers.
 
G

Guest

Just an afterthought . . .

In Excel, there is an "absolute value" command. The syntax is:

ABS(number)

If you ever need to change a whole column of numbers, containing both
positive and negative, into just positives, you can implement this command on
the entire column, and then copy the results (data) back into the original
column. There is absolutely no need to retype numbers just because they are
negative.

This might be helpful, if you need to change the numbers in your database
back into real numbers.
 
G

Guest

TJ - I feel your pain! I have been dealing with this in Access for the better
part of NINE MONTHS! Here is what I do:

I download analytical data from our storage area on the internet as a CSV
(Excel). I DO have an additonal colum for each analyte. So I have a field
named 1, as a text field, the only thing that can go in that column is "<"
then the next field is Benzene for instance, then if the analysis shows a
<0.5, I use a mid function to separate out the "<" from the results then copy
and paste special as values in the "1" column then convert the values in the
Benzene column to a number and remove the "<" with a find and replace
function. This will then leave a number in the benzene column, I know it
seems like a lot of work, but even my instructor at school thought it was a
novel approach.

When I run reports that DO NOT require a calculation I just concatenate the
fields, if a calculation is required, then I can use only the values where
column 1 is null, so that I am only using numbers where a "hit" was detected.
If you want to discuss this further, feel free to contact me off-line:
(e-mail address removed). trust me I have a LOT of experience using Access to
store analytical data.
 
G

Guest

Your suggestion could work, BUT there are rare times when a chemical of
concern is detected AT the threshold level, so the result for sodium could be
returned at 0.10 AND be the detected level, so it would show up WITHOUT the
"<". Using your suggestion that result would not show up as a detection and
therefore the data would be incorrect. That is why I have chosen to use the
extra column in my database, it solves FAR more problems than it causes.
 
G

Guest

I respectfully disagree, Larry. The actual result should be compared as <
threshold, not <= threshold. If actual result = threshold, then it would be
reported as the actual result. If actual result < threshold, then report it
as NDA. It is all in the syntax of the comparison. Generally (and I use
this word purposely, because there can be exceptions), if you keep having to
do work-arounds, it is because there is a flaw in the database design. Make
the database do the work. More time to play minesweeper, that way. ;-)
 
M

mimiandpapaw

Larry G. said:
TJ - I feel your pain! I have been dealing with this in Access for the
better
part of NINE MONTHS! Here is what I do:

I download analytical data from our storage area on the internet as a CSV
(Excel). I DO have an additonal colum for each analyte. So I have a field
named 1, as a text field, the only thing that can go in that column is "<"
then the next field is Benzene for instance, then if the analysis shows a
<0.5, I use a mid function to separate out the "<" from the results then
copy
and paste special as values in the "1" column then convert the values in
the
Benzene column to a number and remove the "<" with a find and replace
function. This will then leave a number in the benzene column, I know it
seems like a lot of work, but even my instructor at school thought it was
a
novel approach.

When I run reports that DO NOT require a calculation I just concatenate
the
fields, if a calculation is required, then I can use only the values where
column 1 is null, so that I am only using numbers where a "hit" was
detected.
If you want to discuss this further, feel free to contact me off-line:
(e-mail address removed). trust me I have a LOT of experience using Access to
store analytical data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top