If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab column heading order
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..... |
#2
|
|||
|
|||
Crosstab column heading order
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','S EP','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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Opal" wrote: 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..... |
#3
|
|||
|
|||
Crosstab column heading order
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" wrote in message ... 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..... |
#4
|
|||
|
|||
Crosstab column heading order
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. |
#5
|
|||
|
|||
Crosstab column heading order
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Opal" wrote: 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. |
#6
|
|||
|
|||
Crosstab column heading order
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. |
Thread Tools | |
Display Modes | |
|
|