ID field with year prefix?

[nancy54]

Hi,
I’m working on a database that tracks incidents. The first form is the Incident Report. If a spill or injury occurs, another form comes up for additional info. There is no unique identifier, so I am relying on the ID field to join the tables. The user will need to enter the ID number (from the Incident Report form) into the subsequent Spill Report form. When the ID becomes lengthy this may pose a problem. Is there a way I can generate a 3-digit number with the year prefix to act as my join field.

 

[June7]

Generating custom unique identifier is a common topic. Have you searched?

 

[MarkK]

Also, generating a custom unique identifier (a franken-code) is commonly considered a waste of time. Just use an auto-incrementing AutoNumber Long Integer as a unique ID, and then save whatever other data there is in actual fields in that row. But mashing fields together to create some human-readable fancy-compilation-number-code-identifier is wasted effort. It makes your life harder, and delivers zero--ZERO--advantage.
imo
Mark

 

[The_Doc_Man]

MarkK's comments are correct. To amplify,...

Typically, generating some sort of time-tagged sequence number so that you have a compound key is just a complication that takes up a lot of time and space. If the compound contains ANY punctuation - such as a dash - then your ID field becomes text and takes up more room than a purely numeric ID.

If you absolutely HAD to have a number, you could do some math using the year with a number that is based on concatenating the year-string with a leading-zero string and then converting that back to a number. Doing it that way, you cannot reliably generate more than six digits (if you put the year in the lead) due to size limitations on LONG variables. That method dies sometime during the year 2147, not that anyone would still be using Access by then.

If you have a synthetic key such as an autonumber as suggested by MarkK, you don't CARE about formatting that number because nobody except Access is supposed to see that anyway. Your incident report number can be stored literally regardless of format and yet it would not affect your system since no record keys would depend on it.

 

[nancy54]

Thank you all for your help and responses.
In some of my research, I've read that the system generated ID field should not be used as the join fields in the database, but in this case there is nothing else that could be used. I decided to use a DMax function on the ID field to call the subsequent form. This way the user does not have to enter the ID at all.
Nancy

 

[Pat Hartman]

The best practice is to use an autonumber internally. Then your franken-key is only used for searching and printing. This allows you to change the generation method for the franken-key without messing up existing records. If the IncidentID comes to you from a different application, it is data. Do NOT use it as the primary key. Just use an autonumber. You do not want to be subjected to the whims of the other application and have to deal with changes they might make to THEIR primary key.