Prev: Edit and save query criteria
Next: select queries that calculate the Median base on group by
From: cinnie on 11 Mar 2010 12:06
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
--
cinnie
From: Duane Hookom on 11 Mar 2010 16:43
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


--
Duane Hookom
MS Access MVP


"cinnie" <cinnie(a)discussions.microsoft.com> wrote in message
news:8E03AE96-50BA-4B6E-B35A-58F84D9F07C7(a)microsoft.com...
> 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
> --
> cinnie

 | 
Pages: 1
Prev: Edit and save query criteria
Next: select queries that calculate the Median base on group by