calculation question (1 Viewer)

Tiki75 · Dec 1, 2008

Please help, I am new to the forum and hope my question makes some sense.
I created a table which has a total of 21 questions each question choices are 0, 1, 2, 3. So say 5 people filled out these question entering either 0-3 for each question.

for question Q1-14:
C1: Count only the questions that have a choice 3

for question Q14-21
C2: Count only the questions that have a choice 3

Example:Total number of 3's counted
C1: = 5,
C2: = 6
Or
C1: = 2
C2: = 5

Then in another column

C3: If C1 is <=5 And C2 is >=6 Enter 3 If Not leave blank

Bilbo_Baggins_Esq · Dec 2, 2008

OK, lets see if I can sort this out

How is your answer table designed?
What do you mean by C1 and C2 and did you notice that your question groups overlap on Q14?

Tiki75 · Dec 2, 2008

Hi Bilbo, thanks for the help
C1, C2, C3 are the columns I would create in the query to list the answers for the calculations I would need. I was trying to seperate the calculations in such a way as to have the count done first.
I just realized that I overlapped the questions, thanks for catching that.
The count questions mean certain questions combined mean something.
So in a sense questions 1-14 give Hyperactive results and 15-21 give inattention results.
The Table layout: ID, EvalYear, Q1.....Q21,
The C1, C2, C3: I would create in the query to list the calculations to count and to have an if statement.
I hope the explaination is ok, please let me know if I didn't explain enough.

Bilbo_Baggins_Esq · Dec 2, 2008

OK, I get that, but how is the answer table laid out?
21 columns (one column for each answer) plus other column(s) for user ID and test ID?

or
Vertically with one column for User ID, Test ID, Question number, Answer number

Tiki75 · Dec 2, 2008

The answer table is laid out 21 questions 1 column for each answer plus other columns for userId

Bilbo_Baggins_Esq · Dec 2, 2008

Assuming the following table:
tbl_Answers:
TestID (P)
Test_Ver
User_ID
Q01
(Columns for Q02 to Q13)
Q14
Q15
(Columns for Q16 to Q20)
Q21

This SQL should work for what you want, but you may need to do some name editing.

Code:

SELECT tbl_Answers.TestID, tbl_Answers.Test_Ver, tbl_Answers.User_ID, IIf([tbl_Answers]![Q01]=3,1,0)+IIf([tbl_Answers]![Q02]=3,1,0)+IIf([tbl_Answers]![Q03]=3,1,0)+IIf([tbl_Answers]![Q04]=3,1,0)+IIf([tbl_Answers]![Q05]=3,1,0)+IIf([tbl_Answers]![Q06]=3,1,0)+IIf([tbl_Answers]![Q07]=3,1,0)+IIf([tbl_Answers]![Q08]=3,1,0)+IIf([tbl_Answers]![Q09]=3,1,0)+IIf([tbl_Answers]![Q10]=3,1,0)+IIf([tbl_Answers]![Q11]=3,1,0)+IIf([tbl_Answers]![Q12]=3,1,0)+IIf([tbl_Answers]![Q13]=3,1,0)+IIf([tbl_Answers]![Q14]=3,1,0) AS C1, IIf([tbl_Answers]![Q15]=3,1,0)+IIf([tbl_Answers]![Q16]=3,1,0)+IIf([tbl_Answers]![Q17]=3,1,0)+IIf([tbl_Answers]![Q18]=3,1,0)+IIf([tbl_Answers]![Q19]=3,1,0)+IIf([tbl_Answers]![Q20]=3,1,0)+IIf([tbl_Answers]![Q21]=3,1,0) AS C2, IIf([C1]<=5 And [C2]>=6,3,"") AS C3
FROM tbl_Answers;

Tiki75 · Dec 2, 2008

Thankyou Bilbo,
I am going to modify what you have sent to fit into an Access query. I'll let you know what happened.

Question: in Access there is a selection for "Select Query" If I put the edited sql code in that window will it work also.

Thank you so much.

calculation question (1 Viewer)

Tiki75

New member

Bilbo_Baggins_Esq

Registered User.

Tiki75

New member

Bilbo_Baggins_Esq

Registered User.

Tiki75

New member

Bilbo_Baggins_Esq

Registered User.

Tiki75

New member

Similar threads

Users who are viewing this thread