Crosstab column heading order

[Opal]

I am creating a crosstab query in Access 2003
as a basis for a report. I want to be able to
sort my column heading but can't figure out
how, is there a way? My SQL is:

PARAMETERS [Forms]![Holdinginfo]![txtHoldSUPNumber] Text ( 255 ):
TRANSFORM First(qryVersatilityTraining.RatingID) AS FirstOfRatingID
SELECT qryVersatilityTraining.EMPNumber,
qryVersatilityTraining.TrainingLineID
FROM qryVersatilityTraining
WHERE (((qryVersatilityTraining.SUPLink)=[Forms]![Holdinginfo]!
[txtHoldSUPNumber]))
GROUP BY qryVersatilityTraining.Position,
qryVersatilityTraining.EMPNumber,
qryVersatilityTraining.TrainingLineID
ORDER BY qryVersatilityTraining.Position
PIVOT qryVersatilityTraining.CourseName;

I have sorted it in my qryVersatilityTraining, but it doesn't carry
the sort over to the Xtab.....

 

[Jerry Whittle]

One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show.

 

[vanderghast]

The column ordering... you mean horizontally? Try using a FORM to display
the result where the controls would have a predefined position, and with an
IN list in the PIVOT clause so the control could be 'bound' to a created
field:


.... PIVOT qryVersatilityTraining.CourseName IN("ClassA", "ClassB", ... )

but note, then, that only the columns in the IN list will ever be created,
and by that, understand that if you in-list does not mention "ClassC", then
that column won't be created, even if there is a CourseName with that value,
in your data.


Vanderghast, Access MVP

 

[Opal]

Jerry,

Isn't that assuming my column headings are
months? Mine are not. They are course
names and I would like them ordered based
on the primary key from their source table.

 

[Jerry Whittle]

You will probably need to manually type in the courses in the order that you
want to see them in the Column Headings. Maybe someone else will come up with
a better idea.

 

[Opal]

Thanks, everyone. That is what I need to do
because it will be a subreport and I can't
get around that. Thanks for the feedback.