Does anyone know how to convert a number between say a centimeter and an
inch without know what type the original number is? I have a problem
here where there are a number of database columns that store these
numbers in either inches/centimeters, pounds/kilos, or some other
combination in a column. What I don't have is another database column
that goes with these numbers to tell me what type the number is in.

The second part to this question is what would the proper solution to
this problem be? Should I go and add a database column for each column
that holds a number to store the type of number that it is?

regards,

Aaron

Aaron Prohaska wrote:

Unless the numbers lend themselves to categorization based on value,
you're up a creek. Without a paddle. And there's a tidal wave
coming. And you boat has a leak....


Don't mix types. Your DB front end may allow entry of either type,
but should convert to a common type. Otherwise, you'll be in the
exact hell that you are now.

Assuming you can categorize the types based on value, my first
choice would be to update the data to all one type and then keep
it that way.

If that absolutely were out of the question,... yeah, I guess a
"descriptor" field would be an option.


--
|_ CJSonnack <Chris@Sonnack.com> _____________| How's my programming? |
|_ http://www.Sonnack.com/ ___________________| Call: 1-800-DEV-NULL |
|_____________________________________________|___ ____________________|

Aaron Prohaska wrote:
To get always a correct answer? Sorry that's not possible, unless you
know atleast something about the sife of the original number.

For example, if you know that all numbers are somewhere between 1.00cm -
1.10cm then you can search all numbers that are 1.00 - 1.10 and marks
them as centimeters and then search all numbers that have different
size, convert them to centimeters and check that they are (after
conversion) between 1.00 and 1.10.

But like I said, without this or some other information is not possible.

So what you are telling here is that you have a lot of data, but you
don't actually know what it is. i.e. You have money, but no one knows
what money it is and therefore you can't buy anything with it.

The smart solution would be to store all numbers in same format. So
don't store centimeters and inches, just store either centimeters or
just inches. Just kilos or just pounds. That way you
a) Don't have that problem
b) It is always easy to convert the number to format that you prefer to
use when you read from database, because you always know in what format
the original number is.

Thank you both very much for the answer which was pretty much the same
for both of you. At least I know what kind of problem I'm facing now
which looks like a fairly large one.

regards,

Aaron

Aggro wrote:

Aaron Prohaska <aaron@NO.SPAM.wrenchscience.com> wrote in message news:<3FBE6DF1.37BC08A6@NO.SPAM.wrenchscience.com> ...
You would need something else to let you know that a given number
is invalid.

Where I've run into this problem is where the people who collect
my data, although are specifically told to measure the groundwater
temperature in Deg F, are too incompetent to realize that their
meter is set to Deg C. In this case, it is obvious when they screw
up because I get values less than 32 (which would be ice and thus,
not a water measurement). Measurements in Deg F could never be
mistaken for Deg C because groundwater temperature is never over 90.

Same thing happens with Specific Conductance. Measurements are
never below 100 umhos, so when we see a log sheet with a reading
of 0.988, we know they had the scale set wrong on the meter and
the reading was actually 988 (at least we assume it is, have to
check the statistics to guess whether they had the meter set for
x100 or x1000).

If your data falls into two distinct distributions, you may be
able to tell which units the distributions are likely to be in.
With groundwater temperature, the distributions do not overlap,
so I can sort them out. But, depending on what you're measuring,
distributions of in/cm or lb/kg may easily overlap, in which
case you're stuck.

That's a good idea although that alone may not solve the problem.
In my example, the units are implied by the field definition, but
I still get

[Temp_deg_F] 12

If I changed it to add a units field, I would probably end up with

[Temp] 12 [Unit] F

which doesn't help. What I have is an input form that does a data
integrity check before it adds a record. It checks for "frozen"
water measurements and automatically makes the correction.

In another database I manage, the units field is required because
we have a generic result field and we need the units to distinguish
between unlike measurements:

[Analyte] Temperature [Result] 50 [Unit] F
[Analyte] Spec Cond [Result] 988 [Unit] umhos
[Analyte] Depth to Water [Result] 12 [Unit] ft BTOC
[Analyte] Water Elevation [Result] 400 [Unit] ft MSL

But again, a data integrity check is still required to prevent
corrupt records from getting into the database. If the units on
the last two lines were reversed, it would be very obvious
since wells aren't 400 ft deep and the water table wouldn't be
12 ft above sea level (not here in Illinois, anyway).

Aggro wrote:

I think we may have found the man who programmed that Mars lander
a couple of years ago.

--
Chuck F (cbfalconer@yahoo.com) (cbfalconer@worldnet.att.net)
Available for consulting/temporary embedded and systems.
<http://cbfalconer.home.att.net> USE worldnet address!