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
|
|||
|
|||
SQL question - Cross Tab query
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 |
#2
|
|||
|
|||
SQL question - Cross Tab query
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; -- Build a little, test a little. "cinnie" wrote: 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 |
#3
|
|||
|
|||
SQL question - Cross Tab query
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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|