Sort race results into order (1 Viewer)

PeterWieland · Jul 2, 2003

Hi,

I am writing a DB for a school sports day, and I have a query that sorts race times into 1st, 2nd 3rd etc down to 8th.

SELECT Results1.EventCode, Results1.Gender, Results1.StudentNo, Results1.Time, (Select Count(*) from Results Where [Time] < [Results1].[Time] and EventCode = [Forms]![frmResultEntry]![lstEvent] and Gender =[Forms]![frmResultEntry]![txtGender]

+1 AS Place
FROM Results AS Results1
WHERE (((Results1.EventCode)=[Forms]![frmResultEntry]![lstEvent]) AND ((Results1.Gender)=[Forms]![frmResultEntry]![txtGender]))
ORDER BY Results1.Time;

This is almost perfect, except that if a competitor does not finish, a time of 0 is recorded, he is given 1st place!

I have tried every which way, but no joy. The closest I have come up with is not to include said competitor, but I really want the result shown with all 8 places.

I am sure the answer is staring me in the face, but I am now at the stage where I can't see the wood for the trees.

Pete

dcx693 · Jul 2, 2003

Instead of using 0 for their time, can you use "DNF" or some other string in a calculated text field? If you sort on that, the number time should still appear on top.

Fizzio · Jul 2, 2003

SELECT Results1.EventCode, Results1.Gender, Results1.StudentNo, Nz([Results1.Time]."DNF") AS FinishingTime, (Select Count(*) from Results Where [Time] < [Results1].[Time] and EventCode = [Forms]![frmResultEntry]![lstEvent] and Gender =[Forms]![frmResultEntry]![txtGender]; ) +1 AS Place
FROM Results AS Results1
WHERE (((Results1.EventCode)=[Forms]![frmResultEntry]![lstEvent]) AND ((Results1.Gender)=[Forms]![frmResultEntry]![txtGender]))
ORDER BY Nz([Results1.Time]."DNF") ;

A word of advice though, I would strongly recommend changing the field name from 'Time' as this is a reserved word in Access and could cause all sorts of problems.

DNF is an example but you could set it to any time / String.

edit: dcx, you read my mind!

dcx693 · Jul 2, 2003

LOL. But you went the extra mile!

Hmm, but if the user has 0 values stored, wouldn't it be better to use some IIf statement instead of Nz?

Fizzio · Jul 2, 2003

I agree it depends on how the data is structured and if there is a one-to-one or one-to-many relationship between competitor and result. The Nz works well for a single table structure (which seems to be the case here) but I have not road tested it on a one-to-many table relationship yet. I'm sure I'll be told if it doesn't work!

PeterWieland · Jul 2, 2003

Hi Guys,

Thanks for the replies.

This ALMOST does what I need, as it sorts the records into the correct order, but I need the 'Place' field to contain the numbers 1 to 8, as it is then exported to a file on another computer for some flashy HTML page as a projected scoreboard. This still leaves the place field with the DNF as first place.

I note your concerns about time Fizzio, but I have simplified the query somewhat from the original. The field is actually called 'TimeDistance' as it is used for long jump etc as well. I just did this for clarity

This is part of a more complex database, with linked tables of student names and event titles, so I am not sure about the NZ/Iif question.

Pete

dcx693 · Jul 2, 2003

This is the key expression regarding the placement:[Time] < [Results1].[Time]

The problem is that if someone is DNF their time gets stored as 0. What if you changed that so that the Time stored becomes something larger than anything within reason for an actual finisher? That way, they DNFs will be counted at the back and all DNFs will have the same numerical standing?

PeterWieland · Jul 2, 2003

DCX, I had thought of doing this, and there is no real reason why I couldn't. I just thought that someone might have come up with a more elegant solution, I can't be the first to write a sports database and come across this problem.

Fizzio · Jul 2, 2003

what if you change
[Time] < [Results1].[Time]
to
[Time] < Nz([Results1.Time]."DNF")

or

[Time] < Nz([Results1.Time]."99:59")

99:59 will depend on your time format of course.

PeterWieland · Jul 3, 2003

Thanks Fizzio, that did the trick!

Sort race results into order (1 Viewer)

PeterWieland

Registered User.

dcx693

Registered User.

Fizzio

Chief Torturer

dcx693

Registered User.

Fizzio

Chief Torturer

PeterWieland

Registered User.

dcx693

Registered User.

PeterWieland

Registered User.

Fizzio

Chief Torturer

PeterWieland

Registered User.

Similar threads

Users who are viewing this thread