Ordering Crosstab Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

By default, my example (and any other), columns are sorted alphabetically. I
want the columns to be shown in order that they were found. eg I have a table
that lists 20 entries per country and want the column headings to reflect the
table.

TRANSFORM First(F1_Grid.Pos) AS FirstOfNum
SELECT F1_Grid.Driver
FROM F1_Dates INNER JOIN F1_Grid ON F1_Dates.Race = F1_Grid.Race
WHERE F1_Dates.Year = ?
GROUP By F1_Grid.Driver
PIVOT F1_Dates.Country;


Driver Australia Bahrain Malaysia San Marino
Christian Klien 13 8 8 17
Christijan Albers 17 18 15 20
David Coulthard 11 13 19 14
Felipe Massa 15 2 21 4
Fernando Alonso 3 4 7 5
Giancarlo Fisichella 2 9 1 11
Jacques Villeneuve 19 11 10 12
Jarno Trulli 9 14 9 9
Jenson Button 1 3 2 2
Juan Pablo Montoya 5 5 5 7
Kimi Raikkonen 4 22 6 8
Mark Webber 7 7 4 10
Michael Schumacher 10 1 14 1
Nick Heidfeld 8 10 11 15
Nico Rosberg 14 12 3 13
Ralf Schumacher 6 17 22 6
Rubens Barrichello 16 6 20 3
Scott Speed 18 16 12 18
Takuma Sato 21 20 17 21
Tiago Monteiro 20 19 16 19
Vitantonio Liuzzi 12 15 13 16
Yuji Ide 22 21 18 22
 
In the absense of any instruction to the contrary, Access will sort the
column in increasing value order.

You can specify an order if you list them in the Column Headings property of
the query (Properties box in query design view.)

If necessary, you could build the query string programmatically, by looping
through a Recordset based on the Dates table to get the IN list.
 
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:

'Australia','Bahrain','Malaysia','San Marino'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as you want. You can change the order as
you want. You can also make data not show up by taking out a column. For
example, remove 'Bahrain' and Bahrain data won't show. And for your needs, it
will create an empty field for the form or report even if there is no
matching data. You could even put in 'Silverstone' and Silverstone will show
up as a column heading even if there are results for that race yet.

Of course this only works if you always know what your column heading need
to be.
 
Back
Top