Sort Crosstab by a custom order

[ps40life]

I have a crosstab query that counts students by gender and breaks that into ethnicity. It works fine, except that the grades don't sort the way I want. An ideal sort would look like this:
EE
PK
K
01
02
03 etc...through 12

Presently, it sorts the numbers first, then alpha with EE, K, PK. Is there a way I can get my ideal??

Thanks!

 

[dhookom]

You need to have a table of grades with one record per grade and fields for Grade and SortOrder. You can then add this table to your crosstab and use the SortOrder field for sorting.

Duane
MS Access MVP

 

[LakotaMan]

Duane,

I have the same situation, but don't quite understand your intstruction. . . could you elaborate?

Thanks,
Tru

 

[dhookom]

You have to store a value in a table that identifies the order you want to use for sorting.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LakotaMan]

Duane,

Okay, got that, but still having problems:

The report is grouped by Department, and I now have a custom sort order on that field that shows up fine in the crosstab. But the report ignores the crosstab sort order and uses the grouping sort order.

I have some custom calculations in the Dept. footer that I need. . . how can I get the report to use the sort order of the crosstab and not the grouping?

Thanks in advance,
Tru

 

[dhookom]

Reports are only reliably sorted using the sorting and grouping dialog in the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LakotaMan]

Is there no way to override that dialog? It only lets me choose ascending/descending. Unlike the query, I don't have a "not sorted" choice.

Thanks,
Tru

 

[dhookom]

You aren't making sense to me. You were concerned that your report wasn't sorted the same as your query and now want to set your report to "not sorted".

I am totally confused ;-)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LakotaMan]

Duane,

I'm sorry to be elliptical. I'll try to phrase this more clearly.

I want the report to use the same order as the crosstab query it's based on. It doesn't. I think the reason it doesn't is that I am grouping in the report and the report is using group sorting to override the query sort.

Does that help any?

Tru

 

[dhookom]

Is there a reason why you can't look at the Order By in the query and enter the same expression in the report? If this doesn't work then give us some good examples of what you have and what you expect.

In a report, grouping implies sorting.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.