Changing a Primary Key

[Purpleswanage]

Please help. I've inherited a database which uses staff numbers as the primary key. Having had these numbers for 30 plus years it was figured that this was a pretty good unique identifier. That was until 1st April (no it wasn't an April Fools prank) when all the finacial systems changed and we were all issued with new staff numbers!

I need to change the existing staff number (PK) to the new one. I've inserted a field with the new staff number for the records but now don't know how, or if indeed it is possible to create an update query that will replace the existing staff number with the new one.

Does anyone have any ideas or suggestions?

As always any help from this forum is very much appreciated.

Kind Regards,

Helen

 

[DCrake]

Simple Software Solutions

How was you given the list of new staff numbers? electronically I hope. If it came via an Excel spreadsheet or and access table you are in luck. Also did it show you both the old and the new numbers for each member of staff. Does it include inactive staff or just current staff? Reason for asking is that if you want to make historic enquiries and you only changed the surrent staff then you will loose the referential integrity of your data.

The simplest way to do it is to import the data into a temp access table and make the OLD staff number a PK.

Create a new query using the existing staff table and the new staff codes. Show the fields Old staff code from both tables, new staff codes from both tables (old table field will be empty).Join them via the OLD code view the data in datasheet mode.

Highlight the new staff codes from the newly created table and select copy. Highlight the blank column from your old table (where the new staff codes are going ) and select paste.

Voiala

Repeat this for all tables that hold the staff number in it. You will need to visit all queries that use the old staff number and replace with new staff number.

As a once only exercise AND BE PERFECTLY HAPPY IN YOUR DECISION TO DO SO, is to delete the old staff number form the table and rename the new staff number field to the old staff number field. This will eleimnate the need to correct queries and forms.

This may seem a bit long winded but any good programmer will first think what can go wrong before doing it. Fail to prepare and prepare to fail.

CodeMaster:

 

[neileg]

After a bit of thought, the easiest way would be to keep your existing PK field, but stop calling it staff number. Use your new staff number field in your forms and reports and change the old field to an autonumber. Any new staff will have a meaningless number in the PK field and existing staff will have their old staff number which has also become meaningless.

 

[twoplustwo]

You could write an append qry and append everything to a new table with the same fields except having StaffID as the first column (PK autonumber). For each record you insert a PK will be generated automatically by the table.