Need Help With Table Design

[tcgardne]

I'm kinda new to Access and learning on my own....so please forgive me
for anything that doesn't sound right.

I have created a table that tracks dogs by number. Some of the dog
numbers start with leading 0's (example 001). I set the dog number
field type to be numerical so when I print a report by dog number, it
will sort them correctly. When I enter 001 it automatically converts
this to 1. I tried changing the field type to text....this keeps the
dog number as 001, but does not allow me to print a report by numerical
order. Is there a way to be able to key in a dog number like 001 and
still be able to print a report by numerical order?

Thanks in advance for any help!!

 

[Jeff Boyce]

I understand that to you those characters look like numbers, but ask
yourself, "am I going to be adding/subtracting/multiplying/dividing 'dog
numbers'?"

I suspect not, just like I suspect that folks who use a "number" data type
for zip codes aren't really planning to do math with them.

In all likelihood, those are text characters, and if you change the data
type to text, your leading zero (character) will be preserved.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

There should be no problem sorting columns containing 3 numeric characters
even if the column is defined as text. However, if you don't always have 3
characters you will run into problems.
You need to make up your mind whether you really want a numeric number. Why
can't you use 1 instead of 001? You can always change 1 to 001 on output by
padding with zeroes.

-Dorian

 

[TGardner]

Jeff, I understand what you're saying about whether it needs to be a
number or text field. You are correct, I will not be adding up the dog
numbers.... but every time a dog crosses, he gets scored. The same dog
may cross 5, 10, 15 times and get scored each time. So I will be
adding up the crossing score (but not the dog #). I tried changing the
dog# to a text field and YES, it does keep the leading zero's....but
when I run my query to give me the total scores by dog number, it gives
me all dog numbers beginning with 0 (0, 00, 001), then gives me all dog
numbers beginning with 1 (1, 10-19, 100-199), then gives me all dog
numbers beginning with 2 (2, 2-29, 200-299), and so forth. I have no
problem with the dog number being a text field....if it could be done
in numerical order, then it would work ok. Sorry if this is
confusing....like I said, I'm new to Access.

Thanks

 

[John Vinson]

it gives
me all dog numbers beginning with 0 (0, 00, 001), then gives me all dog
numbers beginning with 1 (1, 10-19, 100-199), then gives me all dog
numbers beginning with 2 (2, 2-29, 200-299), and so forth.

ummm...

exactly.

001 comes before 100. So does 20.

The text strings

001
020
100
320

will sort correctly, in that order. If you leave off the leading zeros
they will sort as text strings: "1" will sort after "002", because -
alphabetically - they differ at the first position and "0" comes
before "1".

It sounds like you're assuming that putting "1" into a Text field is
exactly the same as putting "001" into a text field. It's not. These
are two different text strings!

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

In order for text "numbers" to sort correctly, they must ALL be the same
length. So, if you have over 100 dogs, you need a three digit number EVEN
for those numbers less than 100. The problem with this of course, is that
if at some point you get to 1000 dogs, you now have to change the length of
your "number" to four digits and you need to change all existing numbers to
also be four digits.

Another alternative, is to use an autonumber as the primary key and in all
relationships and keep dog # as a data field. Assuming that dog numbers are
added in sequence, the autonumber will increment as each new dog is entered.
The autonumber may have gaps so it will not necessarily be equal to the dog
number but you can sort on the autonumber but display the dog number to
achieve the effect you are after.

 

[Tim Ferguson]

I have no
problem with the dog number being a text field....if it could be done
in numerical order, then it would work ok.

I find it hard to think of any good reason to use a text field for this.
The disadvantages are:

- Someone will have to add the leading zeroes. If the user doesn't do it,
then the program will; you will have to create ValidationRule to
constrain the string length to exactly three. Don't forget to ban all
non-numeric characters too; you don't want a user to enter "two" as
a legitimate number

- Revisiting the above point, any failure in the validation will upset
the final ordering. You are right to say that "20" comes before "9"
and after "191". Storing the thing as numeric gets you correct sorting
and obviates need for validation too.

- As a general point, most databases are optimised to use integers as
foreign keys better than text. Granted, "002" takes only eight or so
bytes, but I would trust an integer key more than a text one if there
is no obvious Logical reason to prefer the string.

In any case, sticking a Format property like "000" on a text box control is
about as free-from-overhead as you can probably get. Compare that with the
amount of code you'll need to trap the BeforeUpdate event, defining the
ValidationRule(s), catching the database errors and so on...

Just my tupppenceworth!


All the best



Tim F

 

[Guest]

Just a second to Tim Ferguson recommendation of a format for the field. You
can still use an Autonumber for the field type. If you end up with 10,000,
you update the format to 00000.