Question Primary Key value not currently available

[Beemerang]

Hi All

I am under pressure from the client to import data for individuals into the database despite not having their preassigned unique identity number available for every individual. This ID number is the primary key for the table in which individuals' records are stored.

Can anyone suggest a solution that will allow me to import the individuals with a temporarily assigned ID number but that allows that field to be updated with the correct ID number once it becomes available?

Any suggestions would be appreciated!

Thanks,
Beem

 

[JHB]

How is the ID number created, and how would you determine when the correct ID number becomes available?
If the ID number is created by an autonumber field type, then it could be difficult, (but not impossible).
If would create a field in the table for holding a "flag" to show if the ID number is a temporary ID number.

 

[Beemerang]

How is the ID number created, and how would you determine when the correct ID number becomes available?
If the ID number is created by an autonumber field type, then it could be difficult, (but not impossible).
If would create a field in the table for holding a "flag" to show if the ID number is a temporary ID number.

Hi JHB,

Thanks for the response!

The ID number is user entered (it is similar to a social security number).
As individuals attend training, their id numbers will be collected from them and entered into the database.
Thanks for the flag idea, that is useful!

Beem

 

[gemma-the-husky]

I suspect you are using an ID that has meaning, so you cannot randomly assign a "new ID"?

This is an example of a situation that would be much easier with an autonumber - so for new employees, you can add them to the employee file, leaving the "company ID" blank until you know it.

Your tables would be linked by the autonumber PK, and not by the "meaningful" companyID.

It's probably hard to implement this change in a pre-existing system though.

 

[Beemerang]

I suspect you are using an ID that has meaning, so you cannot randomly assign a "new ID"?

This is an example of a situation that would be much easier with an autonumber - so for new employees, you can add them to the employee file, leaving the "company ID" blank until you know it.

Your tables would be linked by the autonumber PK, and not by the "meaningful" companyID.

It's probably hard to implement this change in a pre-existing system though.

Hi Dave

Thanks for taking the time to reply!

Yep, you are correct. The ID is the equivalent of a social security number issued by the state. Your approach is exactly the way I decided to go, incidentally. I had hoped that at some point I would be able to change this "proper" ID number to be the primary key but due to the relationships it seems that it would be a mammoth task.

Have a good day!

Beem