Order of Row Headers in a cross-tab queriy (Access2003 - 2007)

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

Guest

I have a cross-tab query that works fine as far it concerns the resulting
data. For business reasons, I need to change the order of appearance of the
row headers.
This is the query:

TRANSFORM Sum(tmpRptImp.izdGewTotCorr) AS SumOfizdGewTotCorr1
SELECT tmpRptImp.tmpCount, tmpRptImp.izmDat, tmpRptImp.izmCMR,
tmpRptImp.IM5, Sum(tmpRptImp.izdGewTotCorr) AS [Netto 12,5%],
Sum(tmpRptImp.izdGewTot) AS [Netto Doc]
FROM tmpRptImp
GROUP BY tmpRptImp.tmpCount, tmpRptImp.izmDat, tmpRptImp.izmCMR, tmpRptImp.IM5
PIVOT tmpRptImp.izdGrade;

It returns the row headers in the order:
izmDat, izmCMR, Netto Doc, tmpCount

where I need to see:
tmpCount, izmDat, izmCMR, Netto Doc.

Anyone a suggestion?

Tnx in advance

Ting
 
It sounds as if you have dragged the columns of the query around in
Datesheet View. The simplest method to get the columns to appear in thsame
order as the SQL statement is to create a new query and paste the SQL into
the SQL view. And then save the new query with the name of the old query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Hello John,

Thanks for the answer, this seems to work!

As far as I know (but apparentky this theorema is falling apart;-)) the Jet
engine executes the SQL statement it receives. Where in that statement is
then the order of the row headers? Or is that order passed in another way?

I remarked that if I changed the property 'Caption' of a column (a
Rowheader) that this also isn't reflected in an 'AS NewColName' as I'd
expect. Is this simple to explain to me?

kr

Ting (sorry for time delay, but I'm in GMT+1 timezone (BEL))
 
A crosstab query is a special beast. If you don't specify the columns to be
returned in the pivot column, it runs the query and then creates columns for
each value it finds in the pivot clause. This obviously makes a problem for
any query trying to use the results since the number and names of the
columns that get generated can vary.

As a matter of fact if you run a crosstab and the data does not generate all
the months (using your code) then it will not return a column for any months
that have no data. If you specify the column and there is not data, the
crosstab simply generates the column with null values.

Without writing a much longer explanation, that is the best I can do in a
newsgroup.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top