Sort on total field in CrossTab Query (1 Viewer)

[aldeb]

Below is the sql code I have for a CrossTab Query. Total as GrandTotal
shows GrandTotal of all weekly columns. Is there anyway I can get the
Query Display to sort on the GrandTotal Column?

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-WEEK].Totals) AS SumOfTotals1
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory
ORDER BY Sum([Trends-1-3TON-WEEK].Totals)
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;

 

[namliam]

Yes there is but not within the crosstab...

The trick is to store the crosstab query and make a new one that selects from the crosstab. In this second query you can sort.