I want Access 2007 to create a Primary Key based on the first 4 letters of the Company name and add a number to the end increasing by 1 for additional instances of the same Company.

Example for Company Table

The first record for a Company called Edward & Sons would have a primary key of EDWA01 and the second record for the same company would be EDWA02. I want Access to generate these PKs automatically when a new record is saved and be able to establish if there is already a Company of the same name in the database and add the numbers at the end all by itself. (for branches of the same Company)

Example for Contacts Table (related to the Company table as the many side of the 1 to many relationship)

I want Access 2007 to use the system generated Company Name PK & add a C to it (to stand for contact) and then increase the last digit by 1 each time a new contact is created for that Company. Again I think this is more meaningful than an auto number. Example EDWA01C1 would be contact 1 for Edwards & Son Ltd. EDWA01C2 would be the 2nd contact for the same Company. The contacts will be displayed in a subform of the main form which is the Company form.

I would appreciate help with this as I am about to create the Company table and start putting 1000+ company records in. I am also about to create the contacts table which has more records. Feedback on my way of doing it would be appreciated as this is my first Access database. Is what I am thinking of doing a good idea?

nb As I delete records over time as and when required do the system generated PKs just keep going up and gaps will just appear in the sequence over time.

Simply put, the PK should just be an autonumber and be of no relevance to the users of the database/application

To create custom "references" see the thread here

HTH

Natural v. surrogate keys is an often debated topic. This thread is but one example.

I would personally never use a PK like you describe. It's text, which will make the index slow, and it's a calculated value. What happens when a company changes it name. In Access, your best bet, in my and most other regular contributors here at this site, opinion, is an autonumber PK. You can have an alternate index on another field, but I would not store the first four characters of the name over again. You already have them. You can store a sequential number and concatenate your desired expression any time you want it.

To add to the discussion, what happens when multiple companies start with the same four letters?

Thanks Sleepy Chicken. I have read other threads and am now convinced to use the autonumber.

Thanks Noah, I have read the thread and the big debate and am now convinced to use the autonumber as PK. (it's for Access not us humans lol )

It is, of course, your decision, but, from my own experience, I think you'll find yourself much better off this way.

Thanks Noa