create dummy variables

[bandito]

Hi, I have been trying to create a database where some questions are replied in a form in Access 2003. What I would like to achieve is to create a query that can convert these answers (options:Yes, No, N/A) to numbers (1,-1, 0) (although not all answers have exactly the same value, for example the values of another answer might be (Yes, No, N/A)=(1,-1,-1)) and then take the sum of the results of each record. This will allow me to make an evaluation of each record by using dummy variables. However, the SWITCH expression does not seem to work in this application. I would be grateful if you could make any suggestions.
Thanks in advance.

[nico5038]

As the translation of the N/A can differ, I would start with an additional "steering" table with the QuestionID and the value for the three possibilities like:
QuestionID, TrueValue, FalseValue, NAValue

Next you can create a GroupBy query for the original table, JOINed by the QuestionID and the following IIF's:

select A.QuestionID, Sum(IIF(A.[Answer]=True,B.[TrueValue],IIF(A.[Answer]=False,B.[ValueFalse],B.[NAValue]))) As TotalQuestionValue from tblQuestion A, tblQuestionValues B WHERE A.QuestionID = B.QuestionID GroupBy A.QuestionID;
 

Getting the idea ?

Nic;o)