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.