Order By problem 2

[PaulBricker]

I have a database for our Track and Field coach. The result field in the Results Table is a text field that needs to hold information in a couple different formats. For Track events, the results field looks like
##:##.##
42:05.91 would be an example.
For Field events, the results field looks like
###'##.##
102'22.35 would be an example.
Now if you leave everything alone, the field sorts ascending correctly when ordering by MeetID and EventID. But the problem is we would like to see the Track events sorted Ascending and the Field events sorted Decending. I made two select queries, one for track and one for field, and sorted them in the correct order. Then I joined them together using a Union query but the results are less than stellar. I'm posting the SQL for the Union Query. I have tried all types of ORDER BY statements to no avail. Any thoughts on how to get this field sorted correctly would be appreciated.

SELECT qryResultsField.MeetID, qryResultsField.Opponent, qryResultsField.Date, qryResultsField.EventID, qryResultsField.Event, qryResultsField.Athlete, qryResultsField.Result As MeetResult
FROM qryResultsField
UNION ALL Select qryResultsTrack.MeetID, qryResultsTrack.Opponent, qryResultsTrack.Date, qryResultsTrack.EventID, qryResultsTrack.Event, qryResultsTrack.Athlete, qryResultsTrack.Result As MeetResult 
FROM qryResultsTrack;

Paul

 

[Golom]

Questions:

What do you want Ascending / Descending?

- MeetID?
- EventID?
- Result?

Are there other ORDER BY fields in addition to that one that you want included and, if so, in what order?
(e.g. MeetID, OrderID, Result)

 

[PaulBricker]

MeetID and EventID I need to sort Ascending. Result is the field that I need to order Ascending and Decending. The SQL I had tried was like this

SELECT qryResultsField.MeetID, qryResultsField.Opponent, qryResultsField.Date, qryResultsField.EventID, qryResultsField.Event, qryResultsField.Athlete, qryResultsField.Result As MeetResult
FROM qryResultsField
[blue]Order By Result DESC[/blue]
UNION ALL Select qryResultsTrack.MeetID, qryResultsTrack.Opponent, qryResultsTrack.Date, qryResultsTrack.EventID, qryResultsTrack.Event, qryResultsTrack.Athlete, qryResultsTrack.Result As MeetResult 
FROM qryResultsTrack
[blue]Order By MeetID, EventID, qryResultsTrack.Result;[/blue]

Paul

 

[dhookom]

I would try get rid of the first order by and use

ORDER BY 1, 4, 7 DESC

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

An ORDER BY within a union has no effect. Only the last one does anything. Try

SELECT MeetID, Opponent, Date, EventID, Event, Athlete, Result As MeetResult
FROM qryResultsField

UNION ALL 

Select MeetID, Opponent, Date, EventID, Event, Athlete, Result As MeetResult 
FROM qryResultsTrack

ORDER BY MeetID, EventID,
IIF(Instr(MeetResult,":")>0, -1, 1) * 
Val(Replace(Replace(Replace(MeetResult,"'",""),".",""),":",""))

What this attempts to do is
- Determine if the Event is Track or Field by looking for a ":" in MeetResult
- Convert MeetResult to a numeric value by stripping out all the non-numeric characters.
- Multiply that by -1 for Track Events and +1 for Field Events.

This doesn't change the values of MeetResult. It just computes a value that should sort correctly for each type of event.

 

[PaulBricker]

I'm out of the office till Monday but will look these over when I get back. Thanks for the suggestions.

Paul

 

[PaulBricker]

Good morning. As it turns out, I applied a modified version of Golom's suggestion.
Duane, I couldn't get the values to sort properly using the field numbers, but am going to research them a little further. For example, it did sort field 7 descending, but it also sorted my track values descending which was no help. I did try using values for both the result fields (in this case 7 and 14) but that didn't do it and it may be my lack of experience using this method to sort. Any thoughts would be appreciated.
Golom, your expression was very imaginative. I had considered different ways to create a sort but nothing was as good as yours. What I ended up doing was putting the expression in the underlying queries so that I could simiplify the union query ORDER BY expression. This is how it ended up.
This is my underlying query

MySort: IIf(InStr([Result],":")>0,-1,1)*Val(Replace(Replace(Replace([Result],"'",""),".",""),":",""))

This is the union query

SELECT qryResultsField.MeetID, qryResultsField.Opponent, qryResultsField.Date, qryResultsField.EventID, qryResultsField.Event, qryResultsField.MySort, qryResultsField.Athlete, qryResultsField.Result As MeetResult
FROM qryResultsField
UNION ALL Select qryResultsTrack.MeetID, qryResultsTrack.Opponent, qryResultsTrack.Date, qryResultsTrack.EventID, qryResultsTrack.Event,qryResultsTrack.MySort, qryResultsTrack.Athlete, qryResultsTrack.Result As MeetResult 
FROM qryResultsTrack
ORDER BY MeetID, EventID, [blue] DESC;[/blue]

Thanks again for the suggestions.

Paul

 

[dhookom]

I did try using values for both the result fields (in this case 7 and 14)

There are not 14 columns/fields in the query.

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

As Duane says, there are only seven fields ... or eight with your modified SQL.

I assume that the ORDER BY clause should be

ORDER BY MeetID, EventID, [red]MySort DESC[/red];

[blue]DESC[/blue] is not a field in your query and [blue]DESC[/blue] without a field name is illegal.

You can just put the code for the calculation of [blue]MySort[/blue] in the ORDER BY clause. It doesn't need to be in the SELECT clause.

If you do want to keep MySort as a field in each of the underlying queries where you know the type (i.e. Track or Field), you can simplify it a bit

[blue]In qryResultsTrack[/blue]
MySort: -1 * Val(Replace(Replace([Result],".",""),":",""))

[blue]In qryResultsField[/blue]
MySort:      Val(Replace(Replace([Result],"'",""),".",""))

 

[PaulBricker]

Yes, that ORDER BY statement should have been
MySort DESC;
I understand there aren't 14 fields in the query but have never used numerical values in place of field names when constructing a query so I tried a few different things that didn't make any difference.
I did play around a little more when I had time this afternoon and simplified the IIF statement a little more. Usually I just need a little nudge in the right direction and I can work this stuff thru.
All in all, it's working well and the faculty member that is using this is very please with it.
Really did appreciate the input on this.

Paul