Quickly update table which is using the likert scale

[gojets1721]

I have a table where there's several columns using the likert scale (Strongly agree, agree, neutral, disagree, strongly disagree).

I want to quickly update all the columns so that any instance of 'strongly agree' or 'agree' is changed to 'agreement'. And 'strongly disagree' or 'disagree' is changed to 'disagreement'.

I'm trying to think of the cleanest and easiest way to do this. Any suggestions?

 

[CJ_London]

use an update query

edit: Have to say, would have thought you only need one column, not 5 since the values are mutually exclusive - no-one can say they agree and disagree. They would have to choose neutral or not provide a a response.

Then it would just be a matter of changing the description

 

[gojets1721]

use an update query

I know, but I'm struggling to design it. I'm sorry, I should have said that. I've only ever used an update query to update one column with one term change (i.e. update any instance of 'smith' to 'johnson' in the 'name' field)

There's eight columns with differing terms that need to be updated so I wasn't sure if an update query was the best route and if so, how to design it.

All 8 columns need to be updated to where any instance of 'strongly agree' or 'agree' is changed to 'agreement'. And 'strongly disagree' or 'disagree' is changed to 'disagreement'.

 

[CJ_London]

If you can't normalise the data then an update query is the way to go

I can't design it for you if you don't provide some example data - your description is too vague

for example you say

All 8 columns need to be updated to where any instance of 'strongly agree' or 'agree' is changed to 'agreement'. And 'strongly disagree' or 'disagree' is changed to 'disagreement'.

but your first post implies 5 columns, not 8 and your second implies you have all sorts of values in 8 columns

 

[plog]

The best route is to properly structure your data. Then all those degrees of agreement (strongly disagree, agree, strong disagree, etc.) are in just one column and you run a simple UPDATE query for that single column.

As your data is now, you have to configure and run your query once per column per changed value (Method 1). Or use some conditional logic to write one big query that does it all at once (Method 2).

Method 1:

UPDATE YourTable SET Field1 =  'Agreement' WHERE Field1='strongly agree' OR Field1='agree'

You run that query 16 times (8 fields * 2 Agreement values)

Method 2:

UPDATE YourTable SET Field1 = Iif(Field1='strongly agree' OR Field1= 'agree', 'Agreement', iif(Field1='strongly disagree' OR Field1='disagree', 'Disagreement'), Field2=Iif(Field2='strongly agree' OR Field2= 'agree', 'Agreement', iif(Field2='strongly disagree' OR Field2='disagree', 'Disagreement'), Field3=...

I'm sure I made some syntax error in Method 2, but finding and fixing that or running Method 1 16 times is the pennance you must pay for a poor structure.

 

[gojets1721]

If you can't normalise the data then an update query is the way to go

I can't design it for you if you don't provide some example data - your description is too vague

for example you say

but your first post implies 5 columns, not 8 and your second implies you have all sorts of values in 8 columns

Sorry...so there's 8 fields (columns) titled Question1, Question2, and so on.

In each of the 8 fields, the data follows the likert scale. I've attached an example DB to better explain

 

[gojets1721]

The best route is to properly structure your data. Then all those degrees of agreement (strongly disagree, agree, strong disagree, etc.) are in just one column and you run a simple UPDATE query for that single column.

As your data is now, you have to configure and run your query once per column per changed value (Method 1). Or use some conditional logic to write one big query that does it all at once (Method 2).

Method 1:

UPDATE YourTable SET Field1 =  'Agreement' WHERE Field1='strongly agree' OR Field1='agree'

You run that query 16 times (8 fields * 2 Agreement values)

Method 2:

UPDATE YourTable SET Field1 = Iif(Field1='strongly agree' OR Field1= 'agree', 'Agreement', iif(Field1='strongly disagree' OR Field1='disagree', 'Disagreement'), Field2=Iif(Field2='strongly agree' OR Field2= 'agree', 'Agreement', iif(Field2='strongly disagree' OR Field2='disagree', 'Disagreement'), Field3=...

I'm sure I made some syntax error in Method 2, but finding and fixing that or running Method 1 16 times is the pennance you must pay for a poor structure.

Gotcha. How would you suggest restructuring it? I just posted an example DB if that's helpful

 

[plog]

tblResponses
resp_ID, autonumber, primary key
resp_Survey, number, holds value of Survey Number in there now
resp_Question, number, holds what question number response is for
resp_Answer, text, will hold 'Disagree', 'Agree', 'Neutral' etc.

So those 4 fields will now hold all the data currently in your existing tblResponses. However, instead of 1 record per survey, you will have 8 records per survey (1 per question).

If you ever needed to UPDATE the response you would simply update resp_Answer and you would change all the values appropriately for all questions.

 

[gojets1721]

tblResponses
resp_ID, autonumber, primary key
resp_Survey, number, holds value of Survey Number in there now
resp_Question, number, holds what question number response is for
resp_Answer, text, will hold 'Disagree', 'Agree', 'Neutral' etc.

So those 4 fields will now hold all the data currently in your existing tblResponses. However, instead of 1 record per survey, you will have 8 records per survey (1 per question).

If you ever needed to UPDATE the response you would simply update resp_Answer and you would change all the values appropriately for all questions.

Ahhh gotcha. That makes much more sense. I'll work on that. Thank you!

 

[Pat Hartman]

And while you're at it, if you use a numeric value instead of text for your scale, you'll kill two birds with one stone.
1. the numeric value will sort and compare correctly, which the string will not do
2. If you decide you want to change the text, you don't have to update hundreds or thousands of records,you update ONE record. The one value in the table of options that corresponds with the value you want to change.

1    Strongly agree
2    agree
3    neutral
4    disagree
5    strongly disagree

Just to carry this a little further, if you have multiple different types of value lists to manage, this is a mini-app that I copy into all my new applications. Every app seems to always have several lists that need to be managed and this is a simple way to do it with the minimum amount of fuss.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk