Crosstab query - column display order

[antifashionpimp]

Hello,

I have a crosstab query that has People's names as the rows, and Question descriptions for column headings, e.g. as follows:

-------------Married----Kids---Car----etc. etc.
Pete |
John |
Steve |
etc. |
etc. |

The query runs fine, however, I want the column headings (Married, Kids, etc.) to be sorted according to a number. This number goes from 1 to x in a table called tblQuestions, which looks like follows:

QuestNum--------QuestDescr
1---------------Kids
2---------------Car
3---------------Married

Therefore, I want the column headings in the crosstab query to rather be sorted according to their order as in tblQuestions (i.e. Kids, Car, Married). I tried making my ColumnHeadings's "Sort By" setting in the QBE grid to Ascending, but this does not work. At the moment, the crosstab query sorts them alphabetically.

What must I do?

TIA,

Jean

 

[Pat Hartman]

Open the query in design view and add the column headings to the column headings property in the order in which you want them to appear.

"Kids", "Car", "Married", .....

 

[antifashionpimp]

Thanks for the suggestion Pat, but I was hoping for a simpler way than manually adding all the column headings. tblQuestions has about 120 records, and I don't see the point in adding them all manually.

I could number the question descriptions e.g.

a_a Car
a_b Married
a_c Kids
b_a Salary
etc.
etc.

But that means when adding one more question later, I will have to go and renumber the whole thing!

Can someone please help? Thanks

Jean

 

[Pat Hartman]

I guess if your crosstab is going to produce 120 columns, it would be a pain. But it is only a pain once.

 

[antifashionpimp]

Ok Thanks, I might give it a shot when the users really need it so neat

 

[antifashionpimp]

I gave it a try, and its got a catch:

When I try to add the column headings as you suggested, I get an error saying that the "Text for the given field is too long"

I added the column headings by going into the query's properties, and adding
"Kids","Car", "Married", .... etc. to the Column Headings property.
There are 120 column headings, and I guess that this is too much. Does anyone know what the maximum amount of characters allowed in this property is? And how can I bypass this?

Thanks!

 

[Pat Hartman]

Sometimes different parts of Access have different limits. When you reach the limit in the property save the query and reopen it again in SQL view. See if you can continue adding column headings in that view. You may not be able to reopen the query in QBE view after you make the changes.

If that doesn't work, you're either going to have to live with the alpha sequence or embed a leading sequence indicator so the columns sort as you want to see them.

 

[Nobody]

Hi

I hope this can help you. Go to your crosstab query in design view and replace the field QuestDescr as your column heading for an expression like this:

Exp1: NameofQuery.[QuestNum]&"-"&NameofQuery.[QuestDesc]

(NameofQuery sould be the table or query you are using to build your crosstab query)

Or go to SQL View and replace your PIVOT expression for this:

PIVOT NameofQuery.[QuestNum]&"-"&NameofQuery.[QuestDesc];

--------------------

This will sort your column like this, keeping the order you want:

1-Kids, 2-Car, 3-Married etc

I know, it is not a very elegant way but it works.

Hope this help you

 

[baldeagle]

I'm having the same sorta problem

How do you compensate for the alpha sort putting 1,10,12,2, ect...?

 

[Pat Hartman]

Two choices -
1. separate the numeric and text values into two fields so each can be properly ordered. You can do this in the query or in the table (preferable)
2. Make all the numeric values the same length by using leading zeros. So if your longest number is 12345, your shortest will be 00001 rather than just 1.

 

[Carlos555]

It might be late, but here's a solution, that helped me with a similar problem:
in SQL view you can set the order by amending the code as below(highlighted part is the code to be added):

...
PIVOT YourTable.ColumnheaderFieldName IN (1stItem, 2ndItem...);

 

[gregstemler]

Oh, MS Access, why won't you give us the tools we want???

I've devised my own work around for this problem. Like antifasionpimp, I have a seperate table that defines the order I want the column headers to appear in.

instead of a crosstab query, I create a select query that contains all the fields (citing the original example these would be [Name], [QuestDescr], [value], [QuestNum])

then I make a pivot table (from layout view -> View -> PivotTable View)
Add [Name] as a row field, [QuestNum] and [QuestDescr] as column fields, and [value] as totals or details field.
Play with the order of [QuestNum] and [QuestDescr] in the column fields to get the order you want ([QuestNum] on top).

to get rid of the totals columns and rows, right click on each field name and unselect Subtotals.

Copy all (ctrl+A) then paste to Excel. a few more clicks can delete unwanted rows and headers.

This method works if you want to get your data into excel, but not so much if you want to use the query in an access application.

Otherwise, from everything I've read, the only two ways to get the order of the columns the way you want it is as described above using an IN statement (by several related methods using SQL view, Layout view, or Query properties), or to go all the way and create a Report.

 

[kurios]

Another way (if you are not familiar with SQL) is to use the Column Headings on the Property Sheet and enter the fields in the order you want them to appear.
Eg. In Design View click on the Field that has the Column Heading and enter the column headings you want in the Column Headings property as: "kids, "car"", married" ....

Side effects of using column headings:

Any values you do not list are excluded from the query.

 

[kurios]

Another way (if you are not familiar with SQL) is to use the Column Headings on the Property Sheet and enter the fields in the order you want them to appear.
Eg. In Design View click on the Field that has the Column Heading and enter the column headings you want in the Column Headings property as: "kids, "car"", married" ....

Side effects of using column headings:

Any values you do not list are excluded from the query and ALL the values you list are included.

 

[bluetongue]

If you do not specify the column headings in a crosstab query then any column with no data will not appear.

This can be a problem if you have a report based on the crosstab query or if you want to use the crosstab query as the data source for another query.

It is often easier to paste your SQL into a text editor, paste in the field lists, add the quotes and separators using edit functions and then paste the whole lot back into your SQL.