Do not know how to do this

[Paul Mak]

I need to create a query for a report. The data require for this report will
require more than one table in an Access database. In fact it requires 6
tables and they are all linked togather in a one to many relationship. By
the way this report is to show the statistic of the response from a
question. So the structure of the query is as follow:

Tbl_Program 1 to many to Tbl_Competition 1 to many to Tbl_Survey 1 to many
to Tbl_Response 1 to many to Tbl_Question 1. Also Tbl_Response 1 to many to
Tbl_Participant

Tbl_Question 1 has a PK ID field and FK field for the Tbl_Response. Also
there are 12 sub-questions for this question and I assigned each one of it
as a field in this table. The data type for this sub-question is number
format, it is because in the form user can only select from one of the five
pre-defined option. I use number 1 to 5 to represent each one of the option.

Tbl_Participant has a field called "Category" with 3 different pre-defined
categories.

I want to create a report to show in a specific "Program", a specific
"Competition" and a specific "Survey", the statistic of the response from
each of three category answered on each of the sub-question.

Idealy I would have "Program", "Competition", "Survey", "Category",
"Sub-Question", "Option1", "Option2", "Option3", "Option4"and "Option5" as
the column heading in a query. The query will be "group by" from this fields
and all "Options" column is grouped by "Count". But I do not know how to
achieve this with a query. Thanks.

 

[Steve Schapel]

Paul,

My apologies for a very brief answer... I think a Crosstab Query will
be what you need.

- Steve Schapel, Microsoft Access MVP

 

[paul]

Hi Steve:

Thank you for your response.

I have tried it with the cross-tab query and I just could not get the column
headings I want. Perhaps if you don't mind would you please show me how to
do this. Thanks.

 

[Steve Schapel]

Paul,

The column headings which are automatically generated by a crosstab
query are related to the actual data found in the field which you have
designated as 'Column Heading'. If you want to control the Columns
displayed, and their names, go to the properties of the query
(right-click anywhere on the background of the top panel of the query
design window, and then select Properties from the popup menu which
appears). In the Column Headings property, you can type in the
columns you want. Put your cursor in the Column Headings property
box, and then press F1, for more details how to do this.

- Steve Schapel, Microsoft Access MVP