Custom Primary Key

Question

I am new to access, I am trying to recreate an excel workbook into access. I have a program that uploads flight data to a central server (I will refer to this as program1)  in another location. Currently my pilots track their flight data, offline, in an excel workbook that i created. It is not possible for me to simply import this workbook into access, as there are several pages that autopopulate with complex functions. I want to make a less complicated access database for the pilots to enter their flight data. Program1 also allows me to import a database to upload to the server, but does not allow for an excel workbook. One of the required items for Program1 to accept the database is a custom primary key. I need the primary key to be the first initial + last initial + 4 digit ID number. The table i have created has a seperate column for LastName, FirstName and ID number.

ex. John Doe 1234 would be JD1234.

Is it possible for me to do this, if so how do I do it. I have tried googling for the answer but havnt had any luck.

Answer 1

The export is the 2 initials plus the ID number as a single field.

The 4 digit ID number is their employee ID number and is unique to each person.

PID	LastName	FirstName	MI	EmployeeID
	Doe	John	A	1234

I want the Primary Key (PID field) to auto populate as JD1234.

If that is the case then I do NOT recommend you do that. Use the EmployeeID as a primary key. If you want to display an ID with the initials, then use the expression:

Left(Firstname,1) & LeftLastname,1) & EmployeeID

If you need to join the export to their Access record, create a query on the export with a calculated column:

EmpID: Right(PID,4)

You can then use that query and join it that way.

Answer 2

The export is the 2 initials plus the ID number as a single field.

The 4 digit ID number is their employee ID number and is unique to each person.

PID	LastName	FirstName	MI	EmployeeID
	Doe	John	A	1234

I want the Primary Key (PID field) to auto populate as JD1234.

Answer 3

Lets make sure we are clear. The export from Program1 includes a field that has the 2 initials plus the ID number as a single field and that's what you need to use to join that data to your Access tables? 

Is the 4 digit number unique or are there dups and the addition of the initials is what makes it unique. Once you answer those questions we can explain what the best solution for you is.