Ideas? Looking to replace values with other values

[surayo]

Hello!

I have a database that has sensitive information in one field (company name). Since a version of this database is going to be offered to the general public, I need to replace the existing company name with a generic word or number. I don't want to delete the field outright because it will benefit users to see how often a company engages in a specific activity... I just don't want that to be traced back to the specific company name.

Does anyone have any ideas on how to develop an update query that would replace values? For example, I'd like to have all the records with "Acme Consulting" be replaced with "100"; all records with "John Doe Inc." to be replaced with "101" etc. I've got about 1,000 different companies to replace, so that's why I'm hoping I can automate this process. Otherwise I''ll just have to do it the old-fashioned way by hand.

I'm stumped. Any advice would be greatly appreciated!

Thanks so much.

- Surayo.

 

[RuralGuy]

I would use a RecordSet and a Case statement and walk through the table one record at a time. It would be easier to understand and write.

 

[Zigzag]

Hi Surayo,

From what I have just read it sounds as if your DB needs a little normalising.

I'd like to have all the records with "Acme Consulting" be replaced with "100";

'Acme Consulting' should not be in hundreds or thousands of records, it should be a number that links to a table ID that has 'Acme Consulting' as a supplier or consultant.

 

[RuralGuy]

ZigZag has a good point.

 

[Brianwarnock]

Whilst acknowledging Zigzags point the way I would do this is with several queris and a bitof manual input.
First run a simple make table query , it will have 1 field the company name, group on this, this new table with 1 entry for all companies is now altered in design to include your number field then comes the manual bit as you enter the numbers.

This table is joined to the original on the company field and the query selects all of the fields from the original table but slots the numbers in next to the company. This is so that you can do a quick visual check.

Now remove the company field and rerun the query as a make table query to create the table you require.

Brian

 

[surayo]

THANK YOU Brianwarnock, that sounds like a great idea. I'm going to give it a shot.

Zigzag, I'm not sure how that could be accomplished at present in an easy fashion.

I have about 14,000 records in one table, which link up to make about 5,000 records in another table. One of the fields is CompanyName, although it isn't a very important part of the database (it can be used for grouping movements only), and so I was going to just delete it from the "generic" version for the general public, but then thought, meh, maybe there's a way to just change the name to hide the company identity. But it isn't like each company has thousands of records. It's more like "Ace" is listed 4 times, "John Doe" is listed 3 times, etc. And since the companyName serves no other purpose in this db, I didn't invest a lot of time in making it its own table. Also, this has to be a very simplified, generic db for various levels of access-usage, so I'm trying to keep down the number of linked tables. But I appreciate the idea and will consider it, if I can get some more info on how you would recommend doing that now that the db has the names all in them... since that is my ultimate goal, to replace the names with generic numbers or some other reference that can be cut for the general public.

Thank you everyone!

- Surayo

 

[RuralGuy]

I'm pretty sure a SELECT DISTINCT [Company Name] FROM [YourTable]
...will give you all of the Company Names to add to a New table that has 2 fields: [CompanyID] - AutoNumber, [Company Name] - Text. This will give you the unique numbers for each Company to then go back and replace the Name.