By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

ranking race times in a query

P: 3
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.

kunal
Jan 3 '09 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Regards,
Fish.
Jan 3 '09 #2

P: 3
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
  15.  
  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
  23.  
  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
  34.  
  35. HAVING (tblRaceDrivers.RaceID=[Forms]![frmCreateRace]![RaceID])
  36.  
  37. ORDER BY Sum((([Mins]*60)+[Seconds])/86400);
Thank you,

Kunal
Jan 3 '09 #3

puppydogbuddy
Expert 100+
P: 1,923
Kunal,
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
  3.  
Jan 3 '09 #4

P: 3
puppydogbuddy
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.

kunal
Jan 3 '09 #5

puppydogbuddy
Expert 100+
P: 1,923
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.