MS Access 2007 Rows to columns in recordset

I have a table which is like a questionnaire type .. My original table contains 450 columns and 212 rows. Slno is the person's id who answer the questionaire .

SlNo Q1a    Q1b Q2a Q2b Q2c Q2d Q2e Q2f .... Q37c  <450 columns>
1             1
2                    1                       1         
3     1
4     1                  1
5     1

I have to do analysis for this data , eg Number of persons who is male (Q1a) and who owns a boat (Q2b) i.e ( select * from Questionnaire where Q1a=1 and Q2b=1 ).. etc .. many more combinations are there .. I have designed in MS access all the design worked perfectly except for a major problem ( Number of table columns is restricted to 255 ). To be able to enter this into access table i have inserted in as 450 rows and 212 columns (now am able to enter this into access db). Now while fetching the records i want the record set to transpose the results into the form that i wanted so that i do not have to change m开发者_如何学Goy algorithm or logic .... How to achieve this with the minimum changes ? This is my first time working with Access Database

---

You might be able to use a crosstab query to generate what you are expecting. You could also build a transpose function.

Either way, I think you'll stil run into the 255 column limit and MS Access is using temporary table, etc.

However, I think you'll have far less work and better results if you change the structure of your table.

I assume that this like a fill-in-the-bubble questionnaire, and it's mostly multiple choice. In which case instead of recording the result, I would record the answer for the question

SlNo Q1     Q2
1     B
2            B                                
3     A
4     A      C
5     A

Then you have far fewer columns to work with. And you query for where Q1='A' instead of Q1a=1.

The alternative is break the table up into sections (personal, career, etc.) and then do a join, and only show the column you need (so as not to exceed that 255 column limit).

An way to do this that handles more questions is have a table for the person, a table for the question, and a table for the response

Person
SlNo PostalCode
1    90210
2    H0H 0H0
3


Questions
QID, QTitle, QDesc
1    Q1a     Gender Male
2    Q1b     Gender Female
3    Q2a     Boat
4    Q2b     Car


Answers
SlNo   QID   Result
1      2     True
1      3     True
1      4     True
2      1     True
2      3     False
2      4     True

You can then find the question takers by selecting Persons from a list of Answers

select * from Person
where SlNo in (
    select SlNo from Answers, Questions
    where 
        questions.qid = answers=qid
        and 
        qtitle = 'Q1a'
        and 
        answers.result='True')
and SlNo in (
    select SlNo from Answers, Questions
    where 
        questions.qid = answers=qid
        and 
        qtitle = 'Q2a'
        and 
        answers.result='True')

---

I finally got the solutions

1. I created two table one having 225 columns and the other having 225 column (total 450 columns)

2. I created a SQL statement

    select count(*) from T1,T2 WHERE T1.SlNo=T2.SlNo
   

   and added the conditions what i want

It is coming correct after this ..

The database was entered wrongly by the other staff in the beginning but just to throw away one week of work was not good , so had to stick to this design ... and the deadly is next week .. now it's working :) :)