Primary Key & Unique Numbers with Decimals

[Elaine]

Hello all,

I am having a problem with Microsoft Access 2007. I am using a template and
changed the ID to Job Numbers. However our job numbers use decimals, for
example 104.15. My problem is that even though I can input decimals it
recognizes 104.15 as the same number as 104.00. I can't make it allow
duplicates because I need it as a primary key. Does anyone know how to fix
this so that I can still keep it as a primary key and use it in relationships?

 

[Klatuu]

It may be a data type issue. You should be using a data type that supports
decimals.

 

[Paul Shapiro]

Decimal might be the best datatype. I believe that stores an exact encoded
representation of the decimal number, rather than a floating point
approximation. Oops. I searched for info on the decimal data type and Allen
Browne lists some bugs with it:
http://www.allenbrowne.com/bug-08.html. Seems better to avoid it. If single
precision is not precise enough for you (6 significant digits) you can use
Double which gives 15 digits. However, neither of these is well-suited to
being a PK since they are approximate values.

If your job numbers are "intelligent", for example, 104.15 means that this
job is a subtask of job 104.00, you might reconsider how you represent that
data and whether the job number should be the PK. If the job structures
change, that would mean you have to reassign the PK, which is better
avoided. It's usually a bad design to have an attribute that has any
intelligence within it.

 

[John W. Vinson]

Hello all,

I am having a problem with Microsoft Access 2007. I am using a template and
changed the ID to Job Numbers. However our job numbers use decimals, for
example 104.15. My problem is that even though I can input decimals it
recognizes 104.15 as the same number as 104.00. I can't make it allow
duplicates because I need it as a primary key. Does anyone know how to fix
this so that I can still keep it as a primary key and use it in relationships?

It sounds like you're trying to use a long integer field for this. An integer
is by definition a whole number so that won't work.

I would be leery of using a Double (which *does* allow decimals) as a primary
key but I suppose it's possible; you could also consider using a Currency
(which does allow four decimals and doesn't have the roundoff error problems
that a Double might), or you could use a Text datatype (if you're not doing
arithmatic calculations with the job number).

 

[(PeteCresswell)]

Per John W. Vinson:

I would be leery of using a Double (which *does* allow decimals) as a primary
key but I suppose it's possible;

I've tried that and can attest that it does not work. It's the
approximation thing....

What I did was revert to a Long and apply the decimals myself.

i.e. 265 ==> 2.65.


In general, I would be leery of using a smart number as a PK.
Better, IMHO, to use a dumb number like AutoNumber; keep it
hidden from the user, and index whatever you want to show the
user.

 

[David W. Fenton]

Per John W. Vinson:

I've tried that and can attest that it does not work. It's the
approximation thing....

What I did was revert to a Long and apply the decimals myself.

i.e. 265 ==> 2.65.


In general, I would be leery of using a smart number as a PK.
Better, IMHO, to use a dumb number like AutoNumber; keep it
hidden from the user, and index whatever you want to show the
user.

Or, if you most, a 2-column PK, with the integer part in one column
and the decimal part stored as a whole number in the 2nd column of
the PK.

 

[David W. Fenton]

Maybe I've missed something here, but "Job numbers" sounds an
awful lot like any other simple "ID Number." "Numbers" which
aren't going to be used in mathematical calculations, and I doubt
that these are, really shouldn't be of the numerical datatype at
all, but rather should be defined as ***text***.

The fact that the "number" uses a "decimal" doesn't really change
this. We use diagnostic and procedure codes in medicine that all
the time that consist of "numbers" and "decimals" and they are
never defined as numeric.

Well, seems to me that it all depends on whether or not you need
them to sort as text or numerically. When you're using numbers and
expect them to sort like numbers, then I think you should store them
as numbers (and can format them at runtime if you need leading
zeroes and the like).