Sorting in a Report

[Zippyfrog]

Is it possible to sort different ways in a report based off of a specific header?

Here is what I have. I am working on a database that prints out the best track and field times. For running events, you want the times to be sorted from smallest to largest, for field events, you want the largest throws to be at the top. Currently I have two separate reports for this, and the only difference between the two is that the FinalResult field in the running report is from A to Z, while the Field report is Z to A.

Is there any way I can run a single report, and when a Field Event group comes up, Access knows to sort them in reverse order? In the Group Header, I have a field called "Type" and if Type = Running, then I want the field FinalResult to go from A to Z. But if Type = Field, then I want FinalResult to go from Z to A. Can I accomplish this in Access?

 

[datAdrenaline]

You will have to create a new sorting expression in your Sorting & Grouping dialog box. If you DESIGN your sort to be Ascending, then you will what to NEGATE the values you wish to sort DESCENDING ...

=IIf([Type] = "Running", [FieldValue], 0 - [FieldValue])

----
For example .. the using the values {1,2,3,4,5}

Sorted ASCENDING ...
1
2
3
4
5

NEGATE the values, and still sort ASCENDING
-5
-4
-3
-2
-1

...

Hope that helps!

>>> EDITS ADDED <<<

As an alternative to adding the expression in your Sorting & Grouping dialog, you could add a "SortKey" expression to your query with the same sort of expression syntax, then just add that "SortKey" to the Sorting & Group dialog box ....

SortKey: IIF([Type]="Running", [FieldValue], 0-[FieldValue])

 

[Zippyfrog]

I understand what you are saying conceptually, but to me this solution is an endless loop.
For my Field Value, doesn't it already have to be sorted to know which record is first, which record is second, etc., then to sort it again by the second value messes up the original sorting order. Am I making sense by that? I got the if then statement to work on the report, but it only knew how to assign those numbers because they were already sorted to begin with.

 

[Zippyfrog]

I got it to work. I ended up having to take my FinalTime and using that in my IIF statement. Thanks for your help!

 

[datAdrenaline]

Definately glad to here it ... !!

By the way, can you see why the method I proposed does NOT cause an endless loop? ... what it does is create an expression column, so Access/JET will retreive all the Records, THEN Access/JET will sort the recordset using the value returned by the expression ... so ... there is not an endless loop.

Does that make sense?

 

[Zippyfrog]

Yeah - I understand now. Once I made the sortkey expression in a query, it made a lot more sense. Thanks again for your help!

 

[datAdrenaline]

No problem ... always glad to help!!