replace one value with another in query result

[Joseppi4ever]

I have inherited an MS Access 2003 database which is currently using multiple Yes/No fields to track specific categories of incidents. For example, the table currently has 9 separate category fields called Cat 1, Cat 2, Cat 3, .... Cat 9 that are Yes/No fields.

I want to replace these Yes/No fields with a more descriptive single field in order to more effectively manage the database.

For example, if a record currently has the CAT 1 field set to yes, I want to the new "Category" field to populate with "CAT 1 - Unauthorized Access". If the record has Cat 3 set to Yes, I want the "Category field to populate with "CAT 3 - Malware" etc.

Any assistance would be appreciated.

[ck9663]

--- Sorry...I thought this was a SQL Server question...The following answer is what I would suggest if the back-end is MSSQL ---

Create a new columns for your need so you don't disturb other processes that access the existing columns then just do a simple update statement. Once you are sure that no one is accessing the existing columns you may rename the existing columns into something else and rename the new ones. You might need to change your existing SP, front-end, functions, etc as needed.

Or...you can just create a non-persistent calculated columns that directly use these existing columns to return whatever value you need. Not much storage required, no update needed, you keep all existing process and you can refer to the new columns for your new processes.

Good Luck!!!


~~ CK

[Rabbit]

Your thread has been moved to the Access forum.

And what happens if multiple columns are set to yes?