Conditional Update Query

<<However, once the new numbers are entered in, the query will just duplicate the new numbers, thus bumping out the SSN. >>

Please give more explanation to the above line.

Employee# is the primary key, so there are no duplicates.

I meant, if I just used a plain update query:
UPDATE StaffTest SET StaffTest.EmployeeID = [Employee#];
 Then whenI put in the new Randomly assigned Employee#, the SSN will be totally erased...
does this clear it up?

Hi christie_holtz,

You're going to need a mapping table with two columns, Employee ID and SSN.  The first step will be to fill this table with every SSN in your system and a corresponding employee ID.  Once that is done add (if it doesn't exist yet) an employee ID field to all of your tables that need it and use the map table to update them based on the SSN they contain.  Then remove the SSN field from all tables and adjust your key to include the EmployeeID field.

And it goes without saying that you'll need a backup or two before you do this.


Kelly

I intend to create a new field to house SSN for old employees called EmployeeID, then for new employees just have the EmployeeID equal their Employee#

Will the mapping table work if I need the  fields to equal below:

For old new employees
Employee#=Newly Assigned Random Number
EmployeeID=SSN

For new employees
Employee#=New Assigned Random Number
EmployeeID=The same newly assigned random number

That is the reason why the SSN is getting erased. (SSN)EmployeeID
UPDATE StaffTest SET StaffTest.EmployeeID = [Employee#];
You are replacing the SSN with Employee#

I don't want SSN totally erased though.  I wanted to hide the column

Anyone?

You need a new field on your tables for Employee#
With the mapping table that Kelly suggested you can run a query to update all your tables.
The mapping table should have Employee# and EmployeeID as fields with EmployeeID holding the SSN

UPDATE StaffTest SET StaffTest.Employee# = [MappingTable.Employee#] Where StaffTest.EmployeeID=MappingTable.EmployeeID;

I guess I'm unclear about your overall objective.  In your post you said it was illegal to have SSN in your database but you still want to retain the field?  How does this work exactly?

I am getting an error when I execute the following SQL statement
UPDATE StaffTest SET StaffTest.Employee# = [MappingTable.Employee#] Where StaffTest.EmployeeID=MappingTable.EmployeeID;

I know...it is a very confusing request.  I need to keep the SSN number in the database, but just have the column hidden.

what error are you getting?

Syntax error in Update Statement....then when I press ok the cursor goes to the # on the first Employee#

We have to replace that character with No

Let me try that!

UPDATE StaffTest SET StaffTest.Employee# = [MappingTable].[Employee#] Where StaffTest.EmployeeID=MappingTable.EmployeeID;

Got another error...Invalid bracketing of name '[MappingTable.EmployeeNo]'

UPDATE StaffTest SET [StaffTest].[Employee#] = [MappingTable.Employee#] Where StaffTest.EmployeeID=MappingTable.EmployeeID;

Hopefully when this question is closed the admins can clean all those posts up.  The bottom line is that because you have a 'bad' character in your field name (the 3 sign) you need to bracket the table and the field with square braces.  You can't do this: [mytable.myfield#] but you can do this: [mytable].[myfield#]

Do I need to change the EmployeeNo back to Employee#?

No, it's better to use fields that don't have this conflict.  If you've changed the field name go with that!

Ok...Here is what I am doing.  I am going to the Query section and writing a new query. In design view, I add the two tables to the query....I then go into SQL view and copy the statement, however when I go back to design view....the mappingtable is missing...so then when I try to run the query I get prompts to enter in MappingTable.Employee# and MappingTable.EmployeeID....what am I doing wrong?

I'm sorry....I wrote it wrong....I have changed the field names to EmployeeNo

Thanks Franz,
Actually that is what I have in mind.

Rey:-)

Hey Franz that's what I had in mind also per my post way up there, but I think she wants to permanently keep the ssn field in the database and hide it somehow even after the links are updated.  I have to admit I'm still not clear on exactly why she wants to keep the ssn field after the links have been updated.

Hi Rey,

Lets see, what Christie makes of it. If there is no technical necessety, she should not  keep it. Keeping the mapping of new nuber to ssn as extra-file should be sufficent. You can import it as table with a click, if you need it for some reason in the future.

if for organisational reasons, she will need it in the application, she wiill need to personalize and secure the usage of the mdb, so only specaily privileged users will be able to see this form. of corse in this case the mapping will have to be held in an extra table, so you can secure this table also.

Regards, Franz