Crosstab rows sort order

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

Guest

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
 
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.)
 
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

Allen Browne said:
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.

Tim Long said:
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
Click to expand...
Click to expand...
 
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
 
Fantastic, many thanks again!

Tim Long

Allen Browne said:
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
Click to expand...
 
Back
Top