Crosstab rows sort order

[Tim Long]

Hello, I have a crosstab query/report that displays data by month (columns)
and activity (rows). As the activity data is text, the sort option appears to
be restricted to ascending/descending.. ie alphabetical, but I need the rows
to be listed in my own specified order. Is there any way I can achieve this?

My query is as follows:

TRANSFORM Count(tblDataForCrosstab.Client_ID) AS CountOfClient_ID
SELECT tblDataForCrosstab.Activity
FROM tblDataForCrosstab
GROUP BY tblDataForCrosstab.Activity
ORDER BY MonthName([DataMonth])
PIVOT MonthName([DataMonth]) In
("January","February","March","April","May","June","July","August","September","October","November","December");

I have added a field - a number corresponding to each activity that could
be used for sorting purposes - to the query that populates tblDataForCrosstab
when the report containing the crosstab is run. Could I somehow use the
contents of that column to achieve the correct sort order?

Many thanks in advance

Tim Long

 

[Allen Browne]

Sure. Add the numeric field to your query, and set:
Total Group By
Cross tab Row Heading
Sort Ascending

(You will see the extra column in your query.)

 

[Tim Long]

Many thanks!

Could I also ask how I can force the query to produce a zero in place of a
null for those months with no activity? I thought the Nz function would
apply, but don't know how to apply it to my query. Is this a question for
another thread?

Thanks again

Tim Long

Sure. Add the numeric field to your query, and set:
Total Group By
Cross tab Row Heading
Sort Ascending

(You will see the extra column in your query.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello, I have a crosstab query/report that displays data by month
(columns)
and activity (rows). As the activity data is text, the sort option appears
to
be restricted to ascending/descending.. ie alphabetical, but I need the
rows
to be listed in my own specified order. Is there any way I can achieve
this?

My query is as follows:

TRANSFORM Count(tblDataForCrosstab.Client_ID) AS CountOfClient_ID
SELECT tblDataForCrosstab.Activity
FROM tblDataForCrosstab
GROUP BY tblDataForCrosstab.Activity
ORDER BY MonthName([DataMonth])
PIVOT MonthName([DataMonth]) In
("January","February","March","April","May","June","July","August","September","October","November","December");

I have added a field - a number corresponding to each activity that could
be used for sorting purposes - to the query that populates
tblDataForCrosstab
when the report containing the crosstab is run. Could I somehow use the
contents of that column to achieve the correct sort order?

Many thanks in advance

Tim Long

 

[Allen Browne]

Switch the query to SQL View (View menu.)

On the 2nd or 3rd line, you will see something like this:
SELECT Sum(tblInvoiceDetail.Quantity) AS Total

Change it to:
SELECT Nz(Sum(tblInvoiceDetail.Quantity),0) AS Total

Actually, JET has problems understanding the intended data type returned
from Nz(), so I suggest you wrap the expression in CLng(), CDbl(), or
CCur() - depending whether you want whole numbers, fractional numbers, or
currency. Example:
SELECT CCur(Nz(Sum(tblInvoiceDetail.Quantity),0)) AS Total

 

[Tim Long]

Fantastic, many thanks again!

Tim Long

Switch the query to SQL View (View menu.)

On the 2nd or 3rd line, you will see something like this:
SELECT Sum(tblInvoiceDetail.Quantity) AS Total

Change it to:
SELECT Nz(Sum(tblInvoiceDetail.Quantity),0) AS Total

Actually, JET has problems understanding the intended data type returned
from Nz(), so I suggest you wrap the expression in CLng(), CDbl(), or
CCur() - depending whether you want whole numbers, fractional numbers, or
currency. Example:
SELECT CCur(Nz(Sum(tblInvoiceDetail.Quantity),0)) AS Total