Sort on results of crosstab query

[netrusher]

Below is the code to my crosstab query. I want to sort
descending) on the GrandTotal. I can apply a sort on the
results of the query but cannot sort in the query. It is
my understanding that this is due to this being a crosstab
query. Has anyone any advice on how I can somehow
automatically descend sort on the GrandTotal Column?

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 );
TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, 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, [Trends-1-3TON-WEEK].Problem
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;

 

[dhookom]

You can't have an aggregate function in the ORDER BY clause. You could create a query based on your crosstab that orders by the total.

SELECT *
FROM qxtbYourCrosstab
ORDER BY GrandTotal;

Or, if your results are displayed in a report, the sorting in your query means nothing.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[netrusher]

I am getting the following error"
The Microsoft Jet Database Engine does not recognize " as a valid field name or expression

Below is my SQL code:

SELECT [Trends-1-3TON_Crosstab-WEEK].*
FROM [Trends-1-3TON_Crosstab-WEEK]
ORDER BY [Trends-1-3TON_Crosstab-WEEK].SumOfTotals DESC;

 

[netrusher]

Sorry,

Below is the actual code.

SELECT [Trends-1-3TON_Crosstab-WEEK].*
FROM [Trends-1-3TON_Crosstab-WEEK]
ORDER BY [Trends-1-3TON_Crosstab-WEEK].[Trends-1-3TON_Crosstab-WEEK].[SumOfTotals] DESC;

 

[dhookom]

Read your replies and figure out which should work. A basic understand of SQL should make your issue obvious.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[netrusher]

Thanks everyone, the code below is working

SELECT [Trends-1-3TON_Crosstab-WEEK].*
FROM [Trends-1-3TON_Crosstab-WEEK]
ORDER BY [Trends-1-3TON_Crosstab-WEEK].GrandTotal DESC;

 

[netrusher]

I am definitely trying to get a basic understanding of SQL but I still do not know why I was getting that error.

 

[dhookom]

For some reason you had
[tt][Trends-1-3TON_Crosstab-WEEK].[Trends-1-3TON_Crosstab-WEEK].[SumOfTotals][/tt]
This includes the domain name twice so it is bound to fail. There is no "[Trends-1-3TON_Crosstab-WEEK]" child of "[Trends-1-3TON_Crosstab-WEEK]".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]