Sorting Issue

[eckert1961]

Hello,

I have a report with a Group Header, GradeAttempting, that I want to sort in the following order.

GradeAttempting
Junior 10th Kyu
Junior 9th Kyu
Junior 8th Kyu
Junior 7th Kyu
Junior 6th Kyu
Junior 5th Kyu
Junior 4th Kyu
Junior 3rd Kyu
Junior 2nd Kyu
Junior 1st Kyu
Junior Shodan-Ho
Junior Red 1 Stripe
Junior Red 2 Stripes
Junior Shodan
Adult 10th Kyu
Adult 9th Kyu
Adult 8th Kyu
Adult 7th Kyu
Adult 6th Kyu
Adult 5th Kyu
Adult 4th Kyu
Adult 3rd Kyu
Adult 2nd Kyu
Adult 1st Kyu
Adult Shodan-Ho
Adult Shodan
Adult Nidan
Adult Sandan
Adult Yondan
Adult Godan

The problem that I'm encountering is that the header gets sorted as follows.

GradeAttempting
Junior Shodan-Ho
Junior Shodan-Ho
Junior 9th Kyu
Junior 7th Kyu
Junior 6th Kyu
Junior 5th Kyu
Junior 4th Kyu
Junior 3rd Kyu
Junior 2nd Kyu
Junior 1st Kyu
Adult Yondan
Adult Shodan-Ho
Adult Sandan
Adult Nidan
Adult 9th Kyu
Adult 5th Kyu
Adult 3rd Kyu
Adult 2nd Kyu
Adult 1st Kyu

I have the following set in my report's Sorting and Grouping.

GradeAttempting, Ascending, Group Header=Yes, Group Footer=No, Group On=Each Value, Group Interval=1, Keep Together=No

Additionally, I have no Sort Order set in my report's Record Source query.

Any idea on what I need to change to get the desired results.

Regards,
Chris

 

[gemma-the-husky]

where does it get the grades from?

i would be inclined to add a numeric sort order to the grades table - to force them into the order you want - you wont get the answer with alpha sorts

 

[Kafrin]

If your list of items are fields then you need to add your fields to the report's Sorting and Grouping in the order you want them, with Group Header and Footer both = No.

If your list is the items that can appear in a single field, then you need to introduce a sort order. This can be done in the query behind the report, so you have:

Order: iif(MyField="Junior 10th Kyu",1,iif(MyField="Junior 9th Kyu",2,iif(....)))

Then you sort on the field Order on the report (sorting in the query won't come through to the report).

This may be the quickest approach to implement, but if you are likely to add items to your list or use the same sort order elsewhere then it's clearly not a neat solution.

Ideally, if your items are all stored in a table somewhere, you'll add a number field (probably a byte) to the table called, eg, GradeOrder and fill in numbers against the grades to order them. You can then use this field in your query and report to get the order right.

If you do this, then remember that the order doesn't have to be 1, 2, 3, etc. It's usually best to number them so that the first one you want is 5, then next 10, then 15, 20, etc, so that later on you can add extras into the gaps without having to renumber every subsequent record.

 

[eckert1961]

Hello,

I apologize for the late reply, however, today was my first opportunity to work on this issue again.

I was able to resolve the sorting problem. I added a sortfield to my GradeTypes table and then added the new field to my reports Record Source query. Finally, I added SortField to Sorting and Grouping, set the Sort Order to Ascending.

This works great. Thanks Gemma and Kafrin for your help on this.

Take care.
Chris

 

[gemma-the-husky]

pleased to help, grasshopper