Sorting in Crosstab Queries

[titanl]

Hi,

Is there a way to sort records in crosstab queries? I try but it says:

"Cannot have aggregate function in ORDER BY clause"

Any suggestion?

Thanks.

 

[dhookom]

You can sort by the Row Heading columns in a crosstab. You might want to share your SQL view and tell us which column you want to sort by.

Duane
Hook'D on Access
MS Access MVP

 

[titanl]

Pls find below the SQL view of the crosstab.
I want to sort by the [Total of Points] column

TRANSFORM Sum([qryRankingAndMedalStandings_BestPrimary_Boys/Girls].Points) AS SumOfPoints
SELECT [qryRankingAndMedalStandings_BestPrimary_Boys/Girls].SchoolName, Sum([qryRankingAndMedalStandings_BestPrimary_Boys/Girls].Gold) AS SumOfGold, Sum([qryRankingAndMedalStandings_BestPrimary_Boys/Girls].Silver) AS SumOfSilver, Sum([qryRankingAndMedalStandings_BestPrimary_Boys/Girls].Bronze) AS SumOfBronze, Sum([qryRankingAndMedalStandings_BestPrimary_Boys/Girls].Points) AS [Total Of Points]
FROM [qryRankingAndMedalStandings_BestPrimary_Boys/Girls]
GROUP BY [qryRankingAndMedalStandings_BestPrimary_Boys/Girls].SchoolName
PIVOT [qryRankingAndMedalStandings_BestPrimary_Boys/Girls].SchoolNo;

Thanks.

 

[dhookom]

As you have discovered, you can't sort on a non-group by row heading in the crosstab. You can create a new query based on the crosstab and sort in it:

SELECT * 
FROM qxtbYourCrosstabName
ORDER BY [Total Of Points];

Duane
Hook'D on Access
MS Access MVP

 

[titanl]

Actually this is what did yesterday, but I was just hoping that there could be a way around it.

Thanks again for the time and suggestion, dhookom. You've been of very good help.

titanl