How to convert Colums to Rows (1 Viewer)

[itismesa]

Hi

I have a survey of 10 questions stored in MS-Access table Table1 with 11 columns

PersonName Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10

All questions are multipe choice questions. For some questions the user should choose Yes or NO. However he has to choose amonge 5 answers from 1 to 5

I need to write sql statement the qet me how many Yes or Nos are given to each of question of the first type. And it should gives me for the rest of questions how many choose 1, 2, 3, 4 or 5

We can consider Yes as 1 and NO as 2

for example I expect te result of the query to be someting looks like

Question No (1) (2) (3) (4) (5)

Q1 5 6

Q2 7 3 12 22 1

How to write sql statement qives me that

I appreciate your help

 

[plog]

If you want to do this by SQL alone, you are going to need a sub-query that is a UNION query made of 10 parts (1 for each question):

SELECT "Q1" AS QuestionNo, Q1 AS RespValue FROM Table1
UNION ALL
SELECT "Q2" AS QuestionNo, Q2 AS RespValue FROM Table1
UNION ALL
.
.
.
UNION ALL
SELECT "Q10" AS QuestionNo, Q10 AS RespValue FROM Table1;

Save that query calling it 'subQuery'. Then you can use it in a cross-tab query to get the data like you want it:

TRANSFORM Count(subQuery.RespValue) AS CountOfRespValue
SELECT subQuery.QuestionNo
FROM subQuery
GROUP BY subQuery.QuestionNo
PIVOT subQuery.RespValue;

 

[itismesa]

Yes it works
Thank u very much plog

 

[tariqbablu]

CAN YOU HELP ME PLEASE???

I am making a STUDENT RESULT DATABASE in Access for different classes. Some class have 7 subjects and some 10 and so on,,,
The rule is as follows:

(1).. Passing Marks 33 (Each subject requires 33 marks out of 100 )

(2)..Total 12 Marks will be given as Grace Marks in any limit of subjects if
needed. But in one subject can be given maximum 8 grace marks out of
12 Grace Marks.
(( That means if any subject < 33 to >24 will get grace marks ))

(3)..Maximum 2 subjects will be allowed for re-exam if <24 OR needed
more than the Grace Marks limit.

In query I made expression for Total of all subjects and Count of total Re-exam subject, working fine as below:

Subjects..........Sub1....Sub2....Sub3....Total.....Tot_Grace.....Re-Exam.....Result
Obt. Marks......25........26.......28........79
Grace Mrks......8..........7.........5.......................20.................??................???

In Re-Exam I gave the following code:

sup: IIf([Quran]<[Gracelimit],1,0)+IIf([Hadees]<[Gracelimit],1,0)+IIf([Urdu]<[Gracelimit],1,0)

But, I don’t know how to do for the GRACE MARKS. Please See 4th student. He needs GRACE MARKS 20 which are more than 12. So after 12 he will be eligible for re-exam that means:

Sub1 = Grace Marks (8)
Sub2 = Re-exam
Sub3 = Re-exam

But if we start to give him Grace Marks like:

Sub3 = Grace Marks (5)
Sub2 = Grace Marks (7)
Sub1 = Re-exam

The only thing I need to start giving the GRACE MARKS from the Subject needed minimum Grace Marks.

As you see above example if we start from the first subject, he needs in (2) subjects re-exam and when we started to give the Grace Marks from the Subject requires minimum Grace Marks effected the result and he needs only one subject re-exam.

Please, help me anyone getting this result, if possible through the MODULE.
Thanks
(Tariq)