multiple cross tables in one report from survey questions

[masayoshi hayashi]

Dear Access users

I have a set of questions and corresponding answers to individuals:

ID Q1_1 Q1_2 ....
1 3 text1 ....
2 1 text2 ....
..
..
..

I want to cross tabulate a field to the rest of the questions and
output a report like this:

questionaries Q1_2=1 Q1_2=2 Q1_2=3
Q1_1 sex=male count
sex=female
Q1_3 place=USA
.. place=Canada
.. .
.. .

What is the most efficient way of doing this kind of thing in Access?
In Excel, I create pivot tables for each questions on the second
column "questionaries" then copy Q1_1, Q1_3, ... pivot tables to
another sheet and edit/format in a presentable manner. But this is
repetitive.

Also, is it true that Access cannot act as a database server? In a web
survey, I guess I could create Apache+PHP+MySQL combo and somehow make
PHP generate the above report. What would be the most efficient system
for web surveys?

Thanks for your reply and time.

 

[Duane Hookom]

You might want to look at the sample "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp. This survey is ready
to go for many applications without changing a field, form, query,
report,...

 

[masayoshi hayashi]

Thanks Duane for your reply.

Nice Program! I didn't know it as I am a newbie in Access. I don't
have Access installed at home but from the Word documentation, it
seems that the crosstab report generates only one question v.s.
another? (Much like CrossTab feature or QBE generated result?)

There might be a program that does the task I want, and no need to
reinvent a wheel but for the last two days I have been working on
creating Excel VBA add-in to generate multiple pivot tables from
Access or Excel database. For Access connection, I chose ADO so a user
doesn't need Access installed but only mdb file.