-----Original Message-----
Dear Naveen:
When working with "live" data, certain precautions are well worth
implementing:
1. get all the users off line
2. backup the database
3. Add a new column for the currency value, leaving the existing data
untouched (temporary column)
4. update data into the new column
5. examine the results "side by side" to see how it has done
6. repeat the previous two steps, tweaking as necessary till it's
exactly right
7. remove the original column
8. put in a currency column with the original name
9. update into this new column
10. drop the column you put the data in temporarily
11. thoroughly test. If it fails, restore from backup and try again.
12. only now allow users back in.
Then you're finally ready to "go live." If this frightens you, good!
You should be very much on your toes when fixing live data! This
stuff is why Database Administrators get big bucks, and ulcers! But,
the above is a plan to minimize nasty impacts that might occur.
Once users have begin posting again, you have a mess if it isn't fixed
correctly, so you need to be very confident before you re- open the
database.
If there's a large amount of data, you may not want to spend hours (or
potentially years) checking every row of data. After a quick visual
check, some automated method of checking can be used. Perhaps you
would format both values into strings and show those rows where the
strings are not identical ("20.01" = "20.02"). But do this only if
you did NOT use strings to move the data. You want two completely
different methods, one to convert, another to test.
You could shorten the process to just changing the datatype instead of
steps 3-10, but you can see what safety you would lose. You wouldn't
be able to compare the results to see they are correct!
Perhaps the first question is, how much is the data worth to whoever
owns it. If it were destroyed, how much would it cost to rekey it?
Add to that, how much business would be lost while it was being
rekeyed.
The answer to the above could range from 0 (they wouldn't bother) to
tens of thousands of dollars (or its equivalent in your currency), or
even millions. That answer may affect just how careful a solution you
choose. If the data is almost worthless, then a quick change and
"damn the torpedoes, full speed ahead" may be well justified.
I usually do this (being a engineer at heart). Let's say there's a
one-in-a-thousand chance of screwing things up badly, and lets say the
data is worth $100,000. So, it's worth $100 to make sure it isn't
lost. Maybe you're getting paid about $25/hr. So, it's worth at
least 4 hours to make sure you get it right. However, you'll might
get fired if you blow up a $100,000 database, so probably the real
answer is more like 4 days. You can implement all the steps I
suggested in an hour or two. So, the choice is not so difficult after
all!
Thank you very much for the answer.
But still I have a little problem.
That is , my system has been used over a year without
knowing that problem.
Converting 'single' fields to 'double' has solved the
problem for me.But some values changed by a small fraction.
(for example 20.01 -> 20.0100002289)
Will there be any halmful effect,If I just change
the 'single' data type to 'currency' ?
What is the correct way to convert 'single' fields in
entire database to 'currency' without affecting data ?
Thank a lot.
Naveen
-----Original Message-----
Dear Naveen:
Neither single, nor double datatypes are appropriate
for
a calculation
like this. Both are "scientific" values and very appropriate for
technical calculations, but not for financial. When writing a
financial application you should probably not use them
at
all.
Use currency / money (depending on the database engine you choose,
i.e. Jet or MSDE) for the Price. Your problem will go away, and will
stay away.
Wrong expression result when using SINGLE data
----------------------------------------------
I have an expession in a query like this :
Value:[Price]*[Qty]
It gives an incorrect result when I use 'Single' data type
for the [Price] field.
for example :
Price Qty Value Correct result
20.01 * 1 = 20.0100002288818 20.01
20.01 * 2 = 40.0200004577637 40.02
This is not happening when using 'Double' data type.
Please help me.
Naveen
(I have experienced this problem in Office 2000
Professional(MS Jet SP6), Office XP 2000(MS Jet SP6))
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
.
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
.
