Replacing Text with Numbers

[TanaJayne]

Hello all! I worked extensively with Access and I used to know all of these little tidbits, however, it has been quite a number of years, and I cannot remember this one.

I have a two tables and I want to convert the following text into the corresponding numerical values in both of the tables:

Strongly Agree = 4
Excellent = 4
Very Satisfied = 4
Agree = 3
Good = 3
Satisfied = 3
Disagree = 2
Fair = 2
Dissatisfied = 2
Strongly Disagree = 1
Poor = 1
Strongly Dissatisfied = 1

I had in my head that I could do this in a query. I am thinking I would have to use VBA code, maybe there is a better way.

Any ideas or suggestions??

Thanks
Tana

 

[plog]

Looks like the job for a table to me.

tblScores
Description, Score
Strongly Agree, 4
Excellent, 4
Very Satisfied, 4
....
etc.

Then when you need to convert them, make a query, JOIN your 2 tables and you have replaced the text with the score.

 

[isladogs]

Create & populate a reference table with 2 fields:
e.g. SurveyID (PK - number), SurveyComment (text)

Users then use a combo box with this as the row source to enter their response
The combo box should have the SurveyID as the bound field (hidden)

Add code to the AfterUpdate event to populate the data table with the combobox value (SurveyID)

 

[TanaJayne]

I am dealing with responses that come from JotForm and are dumped into an excel sheet, would this method still work?

Thanks
Tana

 

[The_Doc_Man]

If you import the Excel sheet before processing, the query would never know that it came from Jetform and Excel. If you try to map the Excel worksheet as a table, there can sometimes be problems, but the suggested "JOIN" method should still work.

 

[isladogs]

If you import the Excel sheet before processing, the query would never know that it came from Jetform and Excel. If you try to map the Excel worksheet as a table, there can sometimes be problems, but the suggested "JOIN" method should still work.

Or LINK the Excel file - both should still work fine