469,167 Members | 2,084 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

ranking race times in a query

Hi people this is my first actual post despite being a regular visitor to this website.

I am creating a database where lap times around a GoKarting track are recorded. I have a query that calculates the total time for the race. I need to now create a column that will rank the positions of each driver in each race and another to allocate points accordingly.

for example
1st = 4points
2nd = 3points
3rd = 2points
4th = 1point

i have seen similar queries on here but cannot figure out how to apply it to my scenario.

Any help will be appreciated.

Jan 3 '09 #1
5 1627
2,653 Expert 2GB
Hello, Kunal.

Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudents
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK(tblFamilies)
  4. Name; String
  5. University; String; FK(tblUniversities)
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Jan 3 '09 #2
Thank you Fish for the quick reply

I am new to this forum so do not know how to post in this MetaData format like yours.

I will try my best to explain the tables though

Table containing each individual race = tblRaces
1. RaceID Autonumber
2. TypeID Long Integer
3. Date Date

Table containing each driver assigned to each race = tblRaceDrivers
1. RaceDriverID Autonumber
2. RaceID Long Integer
3. DriverID Long Integer
4. Points Integer (This is for penalty points not points for 1st 2nd 3rd ect)

This contains the lap times for each race = tblLapTimes
1. LapTimeID Autonumber
2. RaceDriverID Long Integer
3. Lap Integer
4. Mins Integer
5. Seconds Integer

Below I have included an SQL view of a query i am using to calculate the total time for each driver in each race [TotalTime].
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(tblRaceDrivers.RaceDriverID) AS CountOfRaceDriverID,
  2.        tblRaceDrivers.RaceID,
  3.        tblRaces.TypeID,
  4.        tblRaceType.RaceName,
  5.        tblRaceType.NoLaps,
  6.        tblRaceType.NoDrivers,
  7.        tblDrivers.DriverID,
  8.        tblDrivers.Forename,
  9.        tblDrivers.Surname,
  10.        tblRaceType.RaceName,
  11.        tblRaces.Date,
  12.        Sum(tblLapTimes.Mins) AS SumOfMins,
  13.        Sum(tblLapTimes.Seconds) AS SumOfSeconds,
  14.        Sum((([Mins]*60)+[Seconds])/86400) AS TotalTime
  16. FROM tblRaceType INNER JOIN
  17.      (tblRaces INNER JOIN
  18.      ((tblDrivers INNER JOIN tblRaceDrivers ON
  19.        tblDrivers.DriverID = tblRaceDrivers.DriverID) INNER JOIN tblLapTimes ON
  20.       tblRaceDrivers.RaceDriverID = tblLapTimes.RaceDriverID) ON
  21.       tblRaces.RaceID = tblRaceDrivers.RaceID) ON
  22.      tblRaceType.TypeID = tblRaces.TypeID
  24. GROUP BY tblRaceDrivers.RaceID,
  25.          tblRaces.TypeID,
  26.          tblRaceType.RaceName,
  27.          tblRaceType.NoLaps,
  28.          tblRaceType.NoDrivers,
  29.          tblDrivers.DriverID,
  30.          tblDrivers.Forename,
  31.          tblDrivers.Surname,
  32.          tblRaceType.RaceName,
  33.          tblRaces.Date
  35. HAVING (tblRaceDrivers.RaceID=[Forms]![frmCreateRace]![RaceID])
  37. ORDER BY Sum((([Mins]*60)+[Seconds])/86400);
Thank you,

Jan 3 '09 #3
1,923 Expert 1GB
In the example below, I am using a separate query to show the rank. If you want to you can make Rank a separate column in your existing query.
See this for detailed step-by-step instructions:
ACC2000: How to Rank Records Within a Query

1. Save your existing query as qryRaceResults.
2. Create a new query using qryRaceResults as the data source:
Expand|Select|Wrap|Line Numbers
  1. Select DriverID,RaceID, Forename, Surname, TotalTime, Count(*) As Rank, IIf(Rank=1,4, IIf(Rank=2, 3, IIf(Rank = 3,2,IIf(Rank=4,1,0)))) As Points From qryRaceResults 
  2. Where [TotalTime] < qryRaceResults.TotalTime + 1
Jan 3 '09 #4
i have done as you said however something is still does not work

below is the sql:
Expand|Select|Wrap|Line Numbers
  1. SELECT qryRaceResults.DriverID, qryRaceResults.RaceID, qryRaceResults.Forename, qryRaceResults.Surname, Count(*) AS Rank
  2. FROM qryRaceResults
  3. WHERE (((qryRaceResults.TotalTime)<[qryRaceResults].[TotalTime]+1));
whenever i try to run the query i receive an error saying:
You tried to execute a query that does not include the specified expression 'DriverID' as part of an aggregate function.

I have done a fair bit of access in my time but this truly has me stumped.

Jan 3 '09 #5
1,923 Expert 1GB
oops! My fault. I believe the general rule is that if you use an aggregate function (like COUNT()) in your select statement, you need to include EVERY column that does NOT appear in an Aggregate function in your GROUP BY clause.

Try adding this group by clause on the line after the where clause.
Expand|Select|Wrap|Line Numbers
  1. Group By qryRaceResults.DriverID, qryRaceResults.RaceID, qryRaceResults.Forename, qryRaceResults.Surname
Jan 4 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Rebecca Smith | last post: by
1 post views Thread by Joseph Bloch | last post: by
5 posts views Thread by valglad | last post: by
5 posts views Thread by Chris | last post: by
reply views Thread by xahlee | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.