Query to Assign Numerical Values to Text Records based on various criteria

[Yoni Hasid]

Hello,
I am struggling with finding a solution on how to assign numerical values to text records based on various criteria. I have a table with survey responses where each column has text records for responses with various criteria. Thus I want to create a query that will change or assign numerical values to this text records as follows:

If response is "Agree", then it is 5
If response is "Somewhat Agree", then it is 3
If response is "Disagree", then it is 1

Each column may have any or all of this responses.

What is the best way to go about it?

[zmbd]

I would create a calculated field using either the switch or choose functions. You can also use a set of nested IIF conditionals.
Links:

Switch Function
Evaluates a list of expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) and returns a Variant value or an expression associated with the first expression in the list that is True.

Choose Function
Selects and returns a value from a list of arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.).

Please understand that most of us are either unable to open/view attachments as we're at work, or will not open an unsolicited attachment just as a matter of Best Safe Computing Practices (esp. #6 and #7). This should in no way be taken personally. It is much better to have a well worded/phrased description of the goal, the current issue, exact errors, and so forth as these will turn up in the search engines for others to find should they be in the same or simular situation as you find yourself in today.

[Yoni Hasid]

Thank you ! So I was able to assign numerical values to fields as follows:

" Switch([My counselor gained my confidence and trust by their knowledge]="Strongly Disagree",1,... ...)

However, how do I or can I permanenetly change data type to a Number in the same string?

[zmbd]

You can make an update query. The basic information is here: Introduction to queries (V2010) Be aware of the fact that if you do update within the same field as the text reply, then the numeric value will be treated as text as it's stored within a text field. You would be well advised to create a numeric field and more than likely a few other changes are required in your database.

However, carefully consider why you need to do this, the query does the value change for you at run time.


Also, and I am going to go slightly off topic here; if you are using a field for each question, then you're going run into a lot of issues in maintaining your database... the field name - [My counselor gained my confidence and trust by their knowledge] - is truly (IMHO) awful... you might like to read thru:> Database Normalization and Table Structures. A well normalized database will save you a ton of time and hassles.

The reason Normalization is so important within a database is best shown by example - trying to keep within what I think you are using:

Each of the following tables would have a primary key that is normally unrelated to the record information.

[tbl_attendancei nfo]
This table would have your student and teacher information, at least name, student id.

[tbl_course]
This table would have the course id and name

[tbl_questions]
This would have the various questions such as your "My counselor gained my confidence and trust by their knowledge"

[tbl_txtresponse]
This would have the text and numeric responses

[tbl_QandA]
This would relate the above tables to each other. Note, in this case, there would not be any text in this table, just numeric values relating back to the primary keys of the other tables.

Now if you need to change a question for grammar or legal requirements, you do so once, without altering any tables NOR any related queries!

Or say that you need to Add 'Totaly Agree = 10, Agree = 8, ..." now you add and change that information within [tbl_txtresponse] because the primary key doesn't change, the original answer recorded in [tbl_QandA] is retained (say it's "Agree") however, the weighting is updated (from a 5 now to an 8) for all of the questions within the table (however, there may be times where you'd want to retain the original value and that is another day).

Your forms and queries will have your lookup fields for the human readable side

OK now that I am totally off topic, I'll stop… if you get into problems normalizing your database we can deal with it in a new thread so that we can keep the questions straight.