Crosstab Query: Sort Column heading based on other column sort

[Kaur]

Hi,


I have created a report based on a cross tab query and would like to
Sort the column heading of the report based on the sort order of
another field.
I have three tables called survey results, survey questions and survey
response choices. Survey response choices has over 800 choices types.
Survey results capture results of all the surveys and has questions,
respondent, response choice, response, responsechoice order which
looks like this
Question Respondent Questionchoice Response Responseorder
1 1 Agree 20%
2
1 1 Strongly Agree 10% 1
1 1 Disagree 30% 3

1 1 Strongly Disagree 10% 4

and so on. With the help of one of google groups I could create a
report where
column heading values change based on the question. My problem is user
wants to see the result set in response order they have identified. In
this case:
Quesion 1 Respondent 1
Strongly Agree Agree Disgaree Strongly Disagree
10% 20% 30% 10%
and so on. The order keeps on changing based on the user input for
Response order for each question in the report.
Since there are over 800 response choices I can not put the response
choices in the Pivot of crosstab query sql view.
Any help would be appreciated.

 

[Duane Hookom]

One method would be to add a prefix to QuestionChoice like:
A) Strongly Agree
B) Agree
.....
I don't believe there is a method for sorting the column headings in a
crosstab other than adding the prefix or specifying the order in the column
headings property.

There might be a possible solution for you in the Crosstab.MDB available at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

 

[Kaur]

Duane,

Thanks for the response. I can achieve the same by concatinating the
two fields (Response choice and response order). Is it possible in the
report I only see the value for response choice not of response order
e.g.

01 Strongly Agree
But in report I see only "Strongly Agree" in column heading.
Thanks for any further help

 

[Duane Hookom]

In a report, you set the label captions to whatever you want.

 

[Kaur]

It won't be possible as I have over 800 reponse choice options that
will be shown on the report based on the question they apply to. Each
question with the relevant response choice and response will be
displayed in two rows. I can not achieve that with captions.

 

[Duane Hookom]

Did you ever look at the download I suggested in my first reply? The
solution is very efficient and create unique column titles for each section
of the report.

 

[Kaur]

Based on the download I could create the unique column titles in the
report. It is the sort order of the column heading that is defaulting
to A-Z or Z-A of column values. I want to display the sort order based
on the order identified by the users.

 

[Duane Hookom]

That's where I think you can use a second column with a sort order. Your
columns are dynamic and the sorting in the subreport used as labels is
dynamic. It may take some fiddling with the record sources, control sources,
and sort order but this should be possible.