Storing Trailing Zeros (Significant Figures) (1 Viewer)

Having a problem storing signifigant figures in a table.

Basically, I need to be able to store trailing zeros after a decimal point. The number of trailing zeros is variable from record to record.

Example - I need to store the following:

25
25.0

25.1
25.10
25.100

While each of the two groups of numbers are the same value, these extra zero carry meaning contributing to the number's precision. I do know that for the moment, I will not be exceeding three places beyond the decimal and numbers will always be positive and less than 10000.

When storing these as a number, forcing zeros after a decimal point by formatting is not acceptable as this changes the data and not all numbers will have the same degree of accuracy.

I suppose I could store these as text and convert them to numbers when I need to perform calculations; however, I would prefer a number format if possible.

I am looking for any insight on the best way to accomplish this.

More information on significant figures:
http://en.wikipedia.org/wiki/Significant_figures

Thanks in advance for you assistance. Adam

The only way I know of is to store them as text.

this is ridiculous

how can trailing zeroes EVER be significant?

if a certain precison is attached to the number, i would store this as a separate field.

Basically, Access doesn't support the concept of significant figures. You will either have to use text or save the figure as a real number (so that trailing zeros are truncated) and store the number of trailing zeros separately. Due to the lack of appropriate tools, I suspect that text values will trun out to be easier.

Dave, as an accountantant, trailing zeros are never important. However, to mathematicians and engineers, they are, since as you suggest, they indicate a level of precision.

As Bob says, you could store them as text.

Alternatively, you could store an additional integer value containing the number of decimal digits, so your examples would be stored as:

Your numbers will be stored as their exact values, but you can get them back in the format you want by means of the expression:
Format([yournumber],"0." & String$([decdigits],"0"))
Yielding:
25.
25.0
25.1
25.10
25.100

If that redundant decimal point (in the first case) is a problem, there might be some different syntax for the format function that will drop it, but if not, this works:

Format([yournumber],IIf([decdigits]>0,("." & String$([decdigits],"0")),"0"))

i didn't say i didnt understand why it was important to know that you had rounded a number like 1.230000001 to a fixed number of decimals. Just that the number itself couldn't convey that information intrinsically, since 1.23, 1.230, 1,2300 etc are the same number

You cant store a NUMBER with any precision different to that which the number format allows was the point I was making. The number itself cant convey that information about itself. (if it did, you could actually just multiply the number by 1000 (for 3 dps) and store as an integer - then scale it down again to use again - but you are stuck with the same probem if you try to do that - the number itself cant tell you how to re-scale it

and it is completely inefficient turning a number into a string and back again. its surely far more efficient to store the number of significant places as a separate field.

I am sure you want to keep the number as a number for calculation purposes.

gemma-the-husky said:

this is ridiculous

how can trailing zeroes EVER be significant?

Click to expand...

That was my initial reaction - I'm pretty sure I was taught at school that significant digits were non-zero by definition, but the article linked by the OP does say that zeroes are considered significant digits when they indicate precision.

I can't immediately think of a situation where I would want to store numbers of variable precision in the same column, but that may be due to the limits of my experience/perspective.

Atomic Shrimp said:

That was my initial reaction - I'm pretty sure I was taught at school that significant digits were non-zero by definition, but the article linked by the OP does say that zeroes are considered significant digits when they indicate precision.

I can't immediately think of a situation where I would want to store numbers of variable precision in the same column, but that may be due to the limits of my experience/perspective.

Click to expand...

i think this is semantics, but i cannot see how the zeroes themselves are significant - what is significant is level of precision to which the calculation was made

so if we write down

23.1

or

23.100

we have written the same number - but if the latter is intended to state that the number was calculated (rounded? truncated?) to 3 significant digits, then we need to store this information elsewhere.

I think we are confusing the fact that we have implicitly recorded this significance factor within the format of the written number - but only because we have the knowledge of our external reference point.

what we have to store is

23.1 (the calcluation)
3 (the precision)

as we have both pointed out

I agree - I think it's probably an engineer's convention/notation - acknowledging that all measurements are inherently imprecise and therefore 25.100 implies it hasn't been rounded beyond the third decimal digit - so it represents a smaller range of possible real-world values:

25.1 could indicate a real-world measurement between 25.05 and 25.14999...
25.100 could indicate only a real world measurement between 25.0995 and 25.1004999...

Sorry, Dave if I came across as a bit patronising, I didn't mean to offend. My basic training is as a mathematician, then became an accountant and worked extensively in egineering. I'm aware of a number of conventions in all three disciplines which use numbers in a different way. You and I don't think twice in interpreting a negative figure as a credit. Isn't that using the format of a number to impart meaning that an engineer wouldn't understand?

Access supports none of these specialised conventions and whatever we do is some kind of workaround.

no offence taken - I thought the thread was quite interesting

no, having given this some more thought, i STILL dont think a negative number implies anything at all, other than it being negative

I am an accountant (FCA) also.

I think with computer data processing the concept of debits and credits is being lost (though we still have double entry) - all we have is transactions that are either posditive or negative. Indeed, in any commercial stuff I write, i would never have a column for debits and a column for credits - just a single column for value. In fact, all we do is make sure that every posting consists of an equal and opposite positive and negative.

I think its easy to think of in that way, although we do need to acknowledge the confusing point that sales are negative!

And as long as we are talking into terms of cartesian geometry (I think that is the right expression), I still think a number is just a number. They are all just points on a line stretching from minus infinity to plus infinity.

I know this changes with other geometries (eg spherical geometry) and I am not enough of a formal mathemetician to understand all the subtleties that this implies - but we are just talking about ordinary arithmetic functions, I think.

gemma-the-husky said:

I think with computer data processing the concept of debits and credits is being lost (though we still have double entry) - all we have is transactions that are either posditive or negative. Indeed, in any commercial stuff I write, i would never have a column for debits and a column for credits - just a single column for value. In fact, all we do is make sure that every posting consists of an equal and opposite positive and negative.

Click to expand...

You might be a good person to ask this question, which has been on my mind for a while:

In translating the double-entry metaphor to a database, is it even necessary to store the second value (given that it must always be the opposite of the first)?

Can't double entry records be adequately recorded by a single value, and a pair of nominal codes (or references)?

When you look at the issue of any differences between two strings, say 1.23 and 1.23000, you must ask yourself a question. WHY do you need the differences? This is leading to an example and corrolary of one of my "Old Programmer's Rules." Access cannot tell you anything you didn't tell it first.

Let's look at the number. According to all rules of pure math, those two numbers, when stored in a REAL (SINGLE or DOUBLE) number, must always equate to the same bit pattern, because storing the string "1.23" or "1.2300" is a mathematical shift of representation. There are rules regarding what one may do in such transformations. To make the REAL number change internal representations based on the number of trailing zeroes in the original representation would make the transformation "unstable." (Meaning worthless for fiduciary operations.) In pure math, the number of trailing zeros occurring to the right of a decimal point is not a valid question because representation and value are different things.

However, when one steps into applied math (i.e. the real world), the number of decimal points you observed become significant. However, you cannot represent the number differently if your medium of storage doesn't accomodate that information. And, of course, Access doesn't. A stored field is either SINGLE or DOUBLE or TEXT or CURRENCY, now and forever. The field has a default number of decimal places, which is a field-wide setting, not a per-record setting.

Storing the number as text does appear to preserve precision, but it is still dangerously misleading to do it that way. If you want to use the number in a computation, you have to use CDbl(x) or CSngl(x) to convert it to a numeric internal format that supports a math operation - and in so doing, you just lost the precision again.

The solution is to step away from the number and realize you have TWO pieces of information: The value itself and the precision with which that information was measured.

The confusion arises because you use the same storage medium (a variable of type SINGLE or DOUBLE) for the number regardless of its precision. For a simple field, you don't have the option to say, "Well, this item has 11 digits, I'll store it in a DOUBLE, but that one in the same field but another record only has 5 digits, I can store that in a SINGLE." In either case, when you store it OR WHEN YOU CONVERT IT FROM TEXT, you lost the precision. So if the precision is important, it becomes a trackable data element.

In fact, if the precision of a specific field in a specific record becomes relevant, it might even become a (minor) normalization violation to attempt to indicate precision without storing it separately. If the content of a given field is variant depending on a number that is not part of the record and is not part of the primary key, this is a serious problem in representation of that record as part of the recordset.

Having spent many years in a field where the accumulated error limit of a number became relevant, I understand why one would wish to retain such data. About six or seven employers ago, we did petroleum pipeline leak detection. Trying to retain too many digits of precision would result in us declaring a fractional-barrel leak. Pipelines only make money when product flows through them, and a leak detection alarm is grounds to stop the flow. So it became really important to know where to truncate deviations from expected values. Every leak alarm that stopped a pipeline cost the customer literally hundreds of dollars a second for the high-volume pipelines.

The long answer to this problem is discussed above. The short answer is, I agree with Atomic Shrimp. If it is important to know precision and that precision varies from record to record, then you must store the precision as a separate field that is part of the record to which it applies.

Amp, beware that Access is NOT going to cooperate with you. To graph something numeric - as a number plot - will ALWAYS lose any indication of precision, even if you retain the digits as text. Further, you can get into sorting anomalies when you use text representation if you aren't careful.

atomoc shrimp asked

In translating the double-entry metaphor to a database, is it even necessary to store the second value (given that it must always be the opposite of the first)?

Can't double entry records be adequately recorded by a single value, and a pair of nominal codes (or references)?

Click to expand...

yes, i think it could in principle - but you would have an awful job disentangling data.
i've seen a system that did this actually, and it was absolutely horrible to use "normally".

The_Doc_Man said:

Amp, beware that Access is NOT going to cooperate with you. To graph something numeric - as a number plot - will ALWAYS lose any indication of precision, even if you retain the digits as text. Further, you can get into sorting anomalies when you use text representation if you aren't careful.

Click to expand...

I don't need to worry about losing indication of precision in graphs as all of the values to be stored will be considered precise enough for simple comparison.

For averages, max, min, and such, I don't need to know exactly how many significant figures should be in my answer as it is largely unimportant to the task at hand.

It is only necessary that I store the values with the proper number of significant figures (a manual task performed at data entry).

Since any calculations I may perform won't be reilant on maintaining significant figures for the output, I think storing as text is the simplest method that will suit my purposes. As this is also a multi-user database, it should also reduce the amount of entry errors by staff (as opposed to entering a number and having a separate field for storing the data on the significant figures). While the separate field could be automated on entry, that requires much more overhead than is necessary for our job.

Thanks for all your input! Adam

amp said:

I don't need to worry about losing indication of precision in graphs as all of the values to be stored will be considered precise enough for simple comparison.

For averages, max, min, and such, I don't need to know exactly how many significant figures should be in my answer as it is largely unimportant to the task at hand.

It is only necessary that I store the values with the proper number of significant figures (a manual task performed at data entry).

Since any calculations I may perform won't be reilant on maintaining significant figures for the output, I think storing as text is the simplest method that will suit my purposes. As this is also a multi-user database, it should also reduce the amount of entry errors by staff (as opposed to entering a number and having a separate field for storing the data on the significant figures). While the separate field could be automated on entry, that requires much more overhead than is necessary for our job.

Thanks for all your input! Adam

Click to expand...

I actually think storing numbers as text is a bad decision.

It all comes dowm to use of cpu time. In general, translating a few numbers to and from text, may not amount to much, but it is there, and it is unnecessary.

But if you have a heavy computational process, (say you are trying to evaluate weather patterns or something) then the overhead of translating "numbers" to and from a text format could really affect the overall process time. ,

eg changing eveey text back to a number, for graphing purposes will be affected. sorting numbers will be affected, as already pointed out.

It would be far more efficient to store the precision in a separate numeric field (as it may not be significant! a lot of the time anyway)

gemma-the-husky said:

yes, i think it could in principle - but you would have an awful job disentangling data.

Click to expand...

I'm not sure - it seems to me like an almost perfect candidate for normalization - if the value in column B is always the sign-reversed opposite of the value in column A, then it should be calculated when required - shouldn't make any difference to the way the data is handled, because a query should be able to present it to the user as if they were two stored balancing values.

gemma-the-husky said:

I actually think storing numbers as text is a bad decision.

It all comes dowm to use of cpu time. In general, translating a few numbers to and from text, may not amount to much, but it is there, and it is unnecessary.

But if you have a heavy computational process, (say you are trying to evaluate weather patterns or something) then the overhead of translating "numbers" to and from a text format could really affect the overall process time. ,

eg changing eveey text back to a number, for graphing purposes will be affected. sorting numbers will be affected, as already pointed out.

It would be far more efficient to store the precision in a separate numeric field (as it may not be significant! a lot of the time anyway)

Click to expand...

I agree - the idea of storing the numbers as text makes me twitch for all sorts of reasons - converting back and forth between the two seems like the sort of thing that will, in some exotic, but eventually likely circumstance, end up corrupting the data.

What it boils down to is this (from a DB purist point of view): The numeric value and the degree of precision (or confidence of precision) for that value are two distinct pieces of data. Two pieces of data should always be stored in two distinct fields.

Obviously I can't stamp my feet and hold my breath until the OP agrees with this stance - and I wouldn't dream of trying - the text approach may in practice be completely adequate and may never go wrong, but it's an imposition of a convention that although valid in its own context, is alien to database table design - it needs to be modelled, not imposed.

gemma-the-husky said:

atomoc shrimp asked



yes, i think it could in principle - but you would have an awful job disentangling data.
i've seen a system that did this actually, and it was absolutely horrible to use "normally".

Click to expand...

Quite agree. Plus double entries are often not equal and opposite on a line by line basis. You can have several positive values and several negative values. So long as the total balances to zero, it's still a valid transaction.

I wonder how many database threads have two chartered accountants posting. Having two FCAs must be even rarer!