Sorting Crosstab Query Values

[Chad]

I have a crosstab query that I would like to sort in Descending order based
on the query's values. I can't seem to figure out an effective way. Below
is my current SQL syntax:

TRANSFORM Sum(qryTest.CountOfMembership_ID) AS SumOfCountOfMembership_ID
SELECT qryTest.Region FROM qryTest GROUP BY qryTest.Region
PIVOT qryTest.Club;

I would like to sort the CountOfMembership_ID in Descending order. If this
can be done any way through this query, please let me know.

Thanks in advance!

Chad

 

[Allen Browne]

Chad, I don't understand what you are expecting.

The CountOfmembership is spread across several columns - one for each club.
There is therefore no way to sort by these numbers: they highest row for one
club might be the lowest for another.

You can sort by the fields used as column headings (Region in your query),
but I don't follow how you can sort by values that are higgledy-piggledy
spread across multiple columns.

 

[Chad]

Allen, I apologize for the confusion. Let me give you some context as to
why I am asking and hopefully it will clear things up.

I am creating a chart on a report that has sum of countofmembership of each
region (the clubs are stacked to get a total of each region). The rowsource
of the chart is the query I pasted below. I would like the chart to display
the region with the highest countofmemberships first and then move down from
there.

I hope that this has helped and if there is any way to do this, please let
me know.

Thanks,
Chad

 

[Allen Browne]

Sorry, Chad, I don't get why we are using a crosstab (instead of just a
query that groups by region) as the source for this report.

Perhaps someone who uses charts more often than I do can help.