Alphanumeric Job Number as Key Field?

[Kevin Sprinkel]

We perform multiple estimates of construction projects as
they are being designed. We number our jobs sequentially
by year, e.g., the 1st job of 2004 is "04001".

From all I've seen, it seems that most designers use an
AutoNumber for the key field, and that using an
alphanumeric field might result in slower performance. I
can think of other competing issues, and I imagine there
are others.

There are several detail tables associated with the Jobs
table, such as Estimates, Consultants, Bids, etc.

Any opinions?

 

[Graham Mandeno]

Hi Kevin

No, there are no significant performance issies with using a short text key
rather than an autonumber. In any case, you could easily store the values
you describe in a numeric long integer field.

More important to consider is how you would generate the next available
number for new jobs. Assuming you also have a JobDate field, it would be
easy enough to use DMax to get the highest existing JobNumber in the current
year.

And of course you must be satisfied that you will never have more than 999
jobs in one year!

 

[Kevin Sprinkel]

Thanks, Graham. Yes, I plan to use DMax.

More important to consider is how you would generate the

next available number for new jobs. Assuming you also
have a JobDate field, it would be easy enough to use DMax
to get the highest existing JobNumber in the current

year.

For now, at least, we're a small consulting firm, but we
can always add another digit!

Best regards.

 

[fofa]

not that big an issue. If you had a realy big text field as a key,
performance could be affected, but for the most part you won't see any
difference in what you are describing.