is it possible to order columns generated by a crosstab query using another query?

I have a crosstab query (code below) that does the following -

matches item & price data or different restaurants from a table called temp_all_items

with a description of the financial quarter in which it was collected (from dim)

and the city name associated with its location ID (from location)

The resulting datasheet "stacks" the location and quarter data so that Q107 Chicago data is all under a column called Q107 CHI, and so on.  The problem is that the columns generated (quarter & location) are not in chronological order, so Q308 ATL might come before Q106 NYC.  The columns need to be shown in chronological order by financial quarter and then in alphabetical order by location.

I'm unable to figure out how to either make the crosstab query show the columns in the order I'd like.  I have a query which has all of the possible column names that could be generated, in the order they should be displayed, and thought that maybe I could use it create another query which took the results in order, but I'm unable to wrap my mind around how I might begin.  The query, location_qtr, looks like this:

Expr1      location_id      city      ID      description
Q105 Atl      81      Atlanta      63      Q105
Q105 Atl      11      Atlanta      63      Q105
Q105 Atl      71      Atlanta      63      Q105
Q105 Atl      16      Atlanta      63      Q105
Q105 Atl      66      Atlanta      63      Q105
Q105 Atl      46      Atlanta      63      Q105
Q105 Atl      61      Atlanta      63      Q105
Q105 Atl      76      Atlanta      63      Q105
Q105 Atl      31      Atlanta      63      Q105
Q105 Atl      21      Atlanta      63      Q105
Q105 Atl      56      Atlanta      63      Q105
Q105 Atl      41      Atlanta      63      Q105
Q105 Atl      1      Atlanta      63      Q105
Q105 Atl      86      Atlanta      63      Q105
Q105 Atl      51      Atlanta      63      Q105
Q105 Atl      36      Atlanta      63      Q105
Q105 Atl      6      Atlanta      63      Q105
Q205 Atl      46      Atlanta      64      Q205
Q205 Atl      11      Atlanta      64      Q205
Q205 Atl      71      Atlanta      64      Q205
Q205 Atl      16      Atlanta      64      Q205
Q205 Atl      66      Atlanta      64      Q205
Q205 Atl      21      Atlanta      64      Q205
Q205 Atl      61      Atlanta      64      Q205
Q205 Atl      86      Atlanta      64      Q205
 . . . you get the picture.

Has anyone done this kind of thing before?Start Free Trial

1:
2:
3:
4:
5:
6:

TRANSFORM First(temp_all_items.price) AS FirstOfprice
SELECT temp_all_items.chain_name AS Chain, IIf([new_flag]<>0,'New','') & IIf([deleted_flag]<>0,'Deleted','') & IIf([back_flag]<>0,'Back','') AS Notes, temp_all_items.category_name AS Category, temp_all_items.item_name AS Item
FROM (temp_all_items LEFT JOIN dim ON temp_all_items.qtr_id = dim.ID) INNER JOIN location ON temp_all_items.location_id = location.location_id
GROUP BY temp_all_items.chain_name, IIf([new_flag]<>0,'New','') & IIf([deleted_flag]<>0,'Deleted','') & IIf([back_flag]<>0,'Back',''), temp_all_items.category_name, temp_all_items.item_id, temp_all_items.item_name
ORDER BY temp_all_items.chain_name, temp_all_items.category_name, temp_all_items.item_name, temp_all_items.item_id, temp_all_items.chain_name
PIVOT [dim]![description] & " " & Left([location]![city],3);