Ordering of Cross Tab Query

[John Dumay]

Hi Folks,

I have been successful in building a dynamic cross tab query based on the
selection of a Column and Row variable. The dataset that results thus has
different Row_Category fields and different Column headings. Before I run the
query the data is sorted by Column and by Row (I have tried vice versa as
well) in the order I want it to appear in the cross tab query. Unfortunately
the cross tab query automatically sorts the data alphabetically without me
asking it to. How can I stop this and get it to sort as I have set it up in
the source table?

As always any help is appreciated.

Regards

John Dumay

 

[Allen Browne]

To sort the columns as you build the dynamic crosstab query statement, use
the PIVOT clause. The way you order the items there will be the order of the
columns in the query.

To sort the rows, you need some mechanism for sorting, as with any query. If
you are sorting by total, must include the field for the row total as
explained here:
Crosstab query techniques: Row Totals:
at:
http://allenbrowne.com/ser-67.html#RowTotal

 

[John Dumay]

Hi Allen,

i have been able to get the rows to sort OK so thanks for that.

But I am still having problems with the Columns. I have ordered the column
data in the source table in the order I want (not alphabetical) but when I
run the cross tab query it still ordrrs every thing alphabetically.

As always your assistance is appreciated.

Regards,

John Dumay

 

[John Dumay]

Hi Allen,

Thanks for your response.

I have been able to sort the rows but the columns are still problematic. I
did what you said and ordered them in the source table in the order i want
them to appear, but they still come out in alphabetical order. Any
suggestions or is this not what you meant?

As always your assistance is appreciated.

Regards,

John Dumay

 

[Allen Browne]

OpenRecordset() on the source table (that is ordered correctly.)
Loop through the reocrds,and build up the PIVOT clause.

The columns of the crosstab will then appear in the correct order.

(The effect is the same as specifying the Column Headings property in the
properties box, in query design of a crosstab.)

 

[John Dumay]

Hi Allen,

Sorry for the return reply, but i am not quite sure where to insert this
OpenRecordSet() instruction. I take it you are sugesting i build the cross
tab dynamically in code in the form or report module. if so could you provide
an example of how this is done?

Many thanks,


John Dumay

 

[Allen Browne]

Sorry for the return reply, but i am not quite sure where to insert this
OpenRecordSet() instruction. I take it you are sugesting i build the cross
tab dynamically in code in the form or report module. if so could you
provide
an example of how this is done?

I assumed you had already done that, as your orignal post started with:
I have been successful in building a dynamic cross tab query ...

This example uses the Orders and [Order Details] tables of the old Northwind
database. Product numbers are down the left. Employee numbers are across the
top. The matrix shows the quantity of each product sold by each employee:

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT [Order Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY [Order Details].ProductID
PIVOT Orders.EmployeeID In (1,2,3,4,5,6,7,8,9);

In the PIVOT clause, the IN lists the valid employee numbers. You build that
part of the string with an OpenRecordset() on the Employee table, looping
through the records to build up the string.

For an example of looping records:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

If the PIVOT field is of type Text (not Number), you need quotes around each
value in the IN. Explanation:
http://allenbrowne.com/casu-17.html

 

[John Dumay]

Hi Allen,

Thanks for the reply. Sorry about maybe the confusion about what I meant by
dynamic. In my case dynamic means that the Column and Row variable differ
depending on the selection made from two list boxes. thus the Column and Row
Headers can be different depending on the selection. I do have a column in my
source data that would sort the data but apparently this cannot be used in
the same manner as the sort data for the rows (it would be nice if Access
allowed this) but for now it doesn't and just sorts everything alphabetically.

My current work around based on some of the info you supplied was to
concactenate the Column_ID (as a text string) and the Column_Name and then do
the TRANSFORM thus my Column headings now look like this:

01XYX; 02BZY; 03JKY

Thus Access sorts on the the Alpha text numbers and puts the columns in the
order i want them. When i want to display the name in the report or form i
use the MID statement to get rid of the leading alpha text characters as I
cycle through the fields

sColumnName = Mid(rsdata.Fields(i).Name, 3)

This returns only the text portion of the column name string and I can now
display in the order I want.

Also I tried to run a SQL command in Access and got n error in that the
TRANSFORM statement is particular to Access.

Not really the best solution, but it is really only two extra lines of code,
1 in the SQL statement in the Cross Tab Query and one on the form or report;
and it works fine.

Thanks for yur help and I have looked up your website and found many useful
tidbits.

Regards,

John Dumay

Sorry for the return reply, but i am not quite sure where to insert this
OpenRecordSet() instruction. I take it you are sugesting i build the cross
tab dynamically in code in the form or report module. if so could you
provide
an example of how this is done?

I assumed you had already done that, as your orignal post started with:
I have been successful in building a dynamic cross tab query ...

This example uses the Orders and [Order Details] tables of the old Northwind
database. Product numbers are down the left. Employee numbers are across the
top. The matrix shows the quantity of each product sold by each employee:

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT [Order Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY [Order Details].ProductID
PIVOT Orders.EmployeeID In (1,2,3,4,5,6,7,8,9);

In the PIVOT clause, the IN lists the valid employee numbers. You build that
part of the string with an OpenRecordset() on the Employee table, looping
through the records to build up the string.

For an example of looping records:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

If the PIVOT field is of type Text (not Number), you need quotes around each
value in the IN. Explanation:
http://allenbrowne.com/casu-17.html