Change Existing Data for Development

[BMC]

Hi

I have 2 # production databases that are populated with personal data.

I want to copy the databases for third party development purposes have ever
I need to change existing data i.e. Personal names to say "John/Jane Doe"

Is there a way of doing this quickly, I have about 25,000 records per
database.

Regards
Barry

 

[John Spencer]

Run a series of update queries to change the data. If you are changing
to a constant value then you might use something like the following.

UPDATE YourTable
SET [LastName] = "Doe"
, [FirstName] = "John"
WHERE Gender = "Male"

UPDATE YourTable
SET [LastName] = "Doe"
, [FirstName] = "Jane"
WHERE Gender = "Female"

UPDATE YourTable
SET [LastName] = "Doe"
, [FirstName] = "Marti"
WHERE Gender Is Null

If you had an SSN you might use a VBA function to scramble the numbers
or just replace all 1's with 2's, all 3's with 4's etc.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Klatuu]

Your question is not clear. Is your database split? If not, it should be.
Then to change data, all you would need to do would be link to a different
back end.

If it is not split, it is a bit of work. You would have to delete all the
tables in your database and import them from the other database, then
reestablish your relationships.

 

[BMC]

Hi John

Thanks for the advice and help.

Regards
Barry

Run a series of update queries to change the data. If you are changing
to a constant value then you might use something like the following.

UPDATE YourTable
SET [LastName] = "Doe"
, [FirstName] = "John"
WHERE Gender = "Male"

UPDATE YourTable
SET [LastName] = "Doe"
, [FirstName] = "Jane"
WHERE Gender = "Female"

UPDATE YourTable
SET [LastName] = "Doe"
, [FirstName] = "Marti"
WHERE Gender Is Null

If you had an SSN you might use a VBA function to scramble the numbers
or just replace all 1's with 2's, all 3's with 4's etc.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi

I have 2 # production databases that are populated with personal data.

I want to copy the databases for third party development purposes have ever
I need to change existing data i.e. Personal names to say "John/Jane Doe"

Is there a way of doing this quickly, I have about 25,000 records per
database.

Regards
Barry