SQL question - Cross Tab query

[cinnie]

hello gurus

I have a Cross Tab query that works fine. The SQL for this query ends with...

PIVOT tblData.Region

I've noticed that the column headings in my query result always show the
Regions in alpha order (eg: East, MidWest, North1, North2, SouthEast). I've
also noticed that I can 'force' the column order by using something like...

PIVOT tblData.Region IN(North1, North2, MidWest, SouthEast, East)

But, this later method presupposes that I know all of the Regions in
advance. If a new Region is added, it won't be in the 'IN' function.

Here is my question - what techniques are available to manipulate the order
of columns for the pivotfield? (for example, suppose I want to show all the
values (unspecified) in the pivotfield in DESC alpha order. Or, assuming the
Regions all have an autonumber RegID in tblReg, how can I get the column
headings to display in this order?)

thank you in advance for any educational tips

 

[KARL DEWEY]

suppose I want to show all the values (unspecified) in the pivotfield in
DESC alpha order
Try this with your table and field names --
TRANSFORM First(tblData.ccc) AS FirstOfccc
SELECT tblData.aaa
FROM tblData
GROUP BY xxx.yyy, xxx.zzz
ORDER BY tblData.Region DESC
PIVOT tblData.Region;

 

[Duane Hookom]

You could write some code that would build the entire SQL statement and
apply it to the SQL property of your saved query. The final line of your
code might be:
CurrentDb.QueryDefs("qxtbYourName").SQL = strSQL

Otherwise you could create a "sort" field in the Region table so your Column
Heading is something like:
PIVOT tblRegions.SortField & tblData.Region