Calculating race finishing positions

[Coheeba]

What I need is a way of calculating finishing positions.

I need a way to make "Finish Place" = "1" for Teri in race 2, "2" for Hannah in race 2, "3" for Amos in race 2, and "4" for Tim in race 2 in the attached sheet.

Let me know if you need anything further. I've been looking at this so long my brain hurts.

Thanks in advance.

[pbaldy]

Search on "ranking" queries. Basically you use a subquery or a DCount() to count the number of records with a time <= to the current record. In your case the race number would be an additional criteria.

[rpeare]

You can also, if your output is a report, simply put in a line count field on your report and order your race finishes by time, the line count field will properly number the finishing positions assuming your sort is what you want.

I made a table called tblRaces, changed your TIME field to RACETIME and your RACE# field to RACENUM. TIME is a reserved word in access and you do not want to use it as a field name. Likewise # is a special character that has meaning in access so you do not want to use it in a field name either.

This query does what you want:

SELECT tblRaces.RaceNum, tblRaces.Racer, tblRaces.RaceTime, DCount("*","tblRaces","[RaceNum] = " & [racenum] & " AND [RaceTime] <=  " & [racetime] & "") AS RacePlace
FROM tblRaces
ORDER BY tblRaces.RaceNum, tblRaces.Racer;

Normally I try to stay away from Domain functions (Dsum, dcount, davg, etc) so consider what you need your final product to be, is it a list of races involving the same people and an overall winner based on placement in a series of races? if so you can do that on a report without using domain functions.

[pbaldy]

Unless I'm missing something, using a field on the report would not handle ties correctly.

[rpeare]

You're right, if you just use a simple line count function. I just assumed there would be no ties, but it can still be done on a report if there are.