Dynamic Unique ID

[Jmasters11]

Hi there everyone, I am a bit of a novice when it comes to Ms Access as haven’t really used it for the last 10 years! I have a question regarding Unique ID. I would like to create a dynamic unique ID whereby the components that form it come from a couple of other fields. I have managed to create a unique number but would like to add other components to it.

Date: If for example 21/04/2020 is selected I would like to see 20 in front of the unique number, 20_5493.



Type: I have a drop down box with the following options D/Re/Ret that I would like to incorporate on the end so ideally would look like 20_5493_Re.

Note underscores are in place to show separation and do not need to form the ID.
Any suggestions or support would be most welcome.

Thankyou

[Bob Fitz]

I would use an "AutoNumber" type field. They make excellent Primary Keys.
I would keep each piece of data in a separate field and then use a "Calcutated Field" in a query to concatenate the data together.
IMHO it will save you trouble in the future.

[Jmasters11]

Thanks Bob,

Thanks for the advice, this is a good solution. One quick Question. for that record if it was renewed the following year would it be possible to keep the unique number but have 21 as the date in front, that way I can search for the connected records through a query? Or would each record still have to have a different number year on year?

[Bob Fitz]

Thanks Bob,

Thanks for the advice, this is a good solution. One quick Question. for that record if it was renewed the following year would it be possible to keep the unique number but have 21 as the date in front, that way I can search for the connected records through a query? Or would each record still have to have a different number year on year?

I would advise against using the AutoID for anything other than as a Unique Identifier (Primary Key). By definition it can not be repeated. You could have a field for a numeric looking piece of data as well as the Primary Key. This could be repeated as often as required. You could initially duplicate the Primary Key or perhaps better would be to create your own incrementing number using DMax()+1 function. It's not difficult. Many here will be willing to help with that if you need help.