Alphanumeric Job Number as Key Field?

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

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?
 
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!
 
Thanks, Graham. Yes, I plan to use DMax.
More important to consider is how you would generate the
Click to expand...
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.
Click to expand...

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

Best regards.
 
Back
Top