Need a Custom PK

[Tac22]

Hello All,

I'm fairly new to access and learning new things everyday...

I'd like to create a Customer_ID private key for my Customer table. I'd like the Customer_ID in this format:



-Last two digits of current year (when record is created)
-First letter of Last_Name
-Three digit autonumber

Example: A new customer (J. Smith) is created in 2019. Custumer_ID = "19S001"

I thought a composite PK was the way to go but, apparently you can't create a composite PK from calculated fields.

Any help, suggestions, or advice would be welcomed.

Thank you.

[ranman256]

why not just use Autonum as primary key in order to create child recs.
and use your Custom field for internal office reference?

[CJ_London]

apparently you can't create a composite PK from calculated fields.

you can't. Generally this approach is not a good one - at least for a primary key. Just use an autonumber primary key and create your calculated key in vba to update the record

Not sure what the benefit is of your key. I presume the idea is that you are effectively limiting yourself to 999 customers per letter? or 999 in total? or 999 new customers per year?

the year part is straight forward enough, but could just as easily be stored as an 'account opened' date which has the potential to be much more informative
I have doubts about your letter - what if the company only has a one word name? or double barrelled name? or the spelling is such you can't determine the required letter - e.g. J.Smith, J. Smith, J Smith
the autonumber part would be accomplished using what is commonly referred to as DMax+1

I'll leave you to ponder - but to find the max number buried in a text string with (presumably) conditional requirements around year and letter for which max you want 18S?, 19S? 19T? is a lot more work

[Micron]

Tac22 - two possible issues related to your post

Auto Numbers and PK's
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm
- http://www.fmsinc.com/free/newtips/primarykey.asp

Calculated fields
- http://allenbrowne.com/casu-14.html

[orange]

Tac22,
Spend some time reviewing database principles to get a good grasp of what the data base software can do for you. In my view your approach could be working against you. Too many new to database try to concoct coding schemes that end up as a mishmash that could have been prevented with the proven - 1 fact, 1 field approach.
See links in my signature for helpful articles.
Good luck with your learning activity.

[Tac22]

Great, practical advice from all. I had a feeling I was trying to over-engineer or over-complicate things. I suppose I just wanted a unique Customer_ID... something other than 001, 002, 003... I guess I don't have to make the Customer_ID the PK for the table though like ranman256 said. Thank you for the prompt replies. I'm glad I registered for an account here.

[Micron]

I guess I don't have to make the Customer_ID the PK for the table

Not sure what that means. somethingID is usually associated with the PK of a table, and is often but not always simply an autonumber field. The only real purpose of that is to virtually guarantee that no 2 records will have the same value. Hopefully you got that from the PK links I posted. Don't confuse what I'm saying with the desire (and often wise choice) to have a meaningful value that identifies the customer via some value such as their name, and to make that a unique index - as long as you know there will never be 2 Acme companies. My former employer designed around that potential by making an abbreviated customer value as the unique constraint. Thus if there could be 2 or more "Smith and Sons Enterprises" one got SmithSonsEnt and the other SmithSonsEnterp or something like that. Maybe that's a bad example, but I can tell you there were a few duplicates.