How to KEEP the last record but delete previous duplicates in A2007

[neelsfer]

I have a race timing program.
In certain races, a team of 2 cyclists may ride together as a team.
Both persons will be timed, but only the last person crossing the line's time, must be kept in the database. The first person crossing the line, must be deleted as a duplicate record, if person crosses within 10 min of the last person.

i have tried the following but is stuck now.

Dim rs1 As Recordset
Dim myRaceTime As Date, myRaceNo As String, x As Long
Set rs1 = CurrentDb.OpenRecordset("RacetimingT", dbOpenDynaset)
 
x = 0
rs1.MoveLast
With rs1
    Do Until .EOF
        myRaceNo = !RaceNumber   'racenumber is a numberfield type and of a cyclist
        myRaceTime = !RaceFinishTime  'the finish time of the cyclists
        .MoveNext
            Do Until .EOF
                    If (!RaceNumber = myRaceNo) And _
                        (DateDiff("n", myRaceTime, !RaceFinishTime) > -10 And _
                        DateDiff("n", myRaceTime, !RaceFinishTime) < 10) Then
                            .delete
                    End If
                        .MoveNext
            Loop
                .MoveLast
                x = x + 1
                .Move x
    Loop
End With
rs1.close
Set rs1 = Nothing
 
 
 End With

Any suggestions please?

[NeoPa]

Neels, may I ask why the slower time only is recorded?

[neelsfer]

Neopa
We have teams of 2 persons cycling together in a special type of mountain bike races called stage racing (almost like the Tour de France) , and they are suppose to finish together, not more than 2 min apart. This is normal practice then to keep the 2nd person's finish time for the reporting. In normal races everybody finishes in their own time with only one result.
I have sql code that works in a query, but it deletes anything before the last person and not within a specified time.

DELETE racetimingT.racetimingID, racetimingT.RaceNumber
FROM racetimingT
WHERE (((racetimingT.racetimingID)<>(SELECT Max(racetimingid) AS MaxOfracetimingid FROM racetimingt AS Dupe      
   WHERE (Dupe.racenumber = racetimingT.Racenumber)      
  and (Dupe.racename = racetimingT.Racename)                    
   AND (Dupe.racenumber = RacetimingT.racenumber))));
 

[NeoPa]

Thank you. That puts the question into context at least :-)

From your posted SQL I'm assuming the important data is all found within table [RaceTimingT]. There appear to be three fields ([RaceName], [RaceNumber] & [RaceTimingID]) but it isn't clear to me, without the experience in racing, what they refer to. I might guess that the [RaceName] identifies the race itself; The [RaceNumber] idenifies a contestant or team & the [RaceTimingID] identifies the record where a time is logged. There appears to be nothing which indicates what the actual time is though. This seems to be important information, without which it's impossible to determine which records are the quicker times and whether they fall within the ten minute window or not.

Am I close to understanding this question at all?

[neelsfer]

Hi Neopa
In my previous posting the actual finish time is in a field called Racefinishtime and it is = now() and the RaceNumber is a unique number for the contestant and as you correctly noticed the RaceName is the specific event.
The code below is from another form where the first record is kept, and all subsequent duplicate records are deleted within 3 minutes of the first record. I want to achieve the REVERSE of that now.
(with the code i mentioned now, i use a RFID reader which scan a chip with the contestants Racenumber on it multiple times, and only the first record must be kept)

Dim rs1 As Recordset
Dim myRaceTime As Date, myRaceNo As String, x As Long
Set rs1 = CurrentDb.OpenRecordset("RaceEntry2", dbOpenDynaset)
x = 0
rs1.MoveFirst
With rs1
    Do Until .EOF
        myRaceNo = !RaceNumber   'racenumber is a numberfield type
        myRaceTime = !RacefinishTime
        .MoveNext
            Do Until .EOF
                    If (!RaceNumber = myRaceNo) And _
                        (DateDiff("n", myRaceTime, !RacefinishTime) > -3 And _
                        DateDiff("n", myRaceTime, !RacefinishTime) < 3) Then
                            .delete
                    End If
                        .MoveNext
            Loop
                .MoveFirst
                x = x + 1
                .Move x
    Loop
End With
rs1.close
Set rs1 = Nothing

Hope it is more clearer now

[NeoPa]

In my previous posting the actual finish time is in a field called Racefinishtime

I'm sure you're right Neels, but I'd rather have such important information expressed clearly in the question than have to dig through various bits of code to find it for myself. That's a very important point to consider when posting future questions, of course. We have it now anyway, so all is good.

One specific point isn't too clear yet though :
You refer to a contestant's RaceNumber being scanned multiple times. From this I deduce that a contestant, in your terminology, is a team rather than an individual racer. Is that correct? I will proceed as if it is for now.

From your posted code (which seems to remove all records within 3 minutes either way BTW - not just records up to 3 minutes afterwards), if you remove the effect of line #14 and convert the check to use -10 instead of -3, it should do what you need.

In SQL, you might want something like :

DELETE
FROM   [RaceTimingT] AS [tRT]
WHERE  ([RaceTimingID] In(SELECT DISTINCT
                                 tRT1.RaceTimingID
                          FROM   [RaceTimingT] AS [tRT1]
                                 INNER JOIN
                                 [RaceTimingT] AS [tRT2]
                            ON   tRT1.RaceName=tRT2.RaceName
                           AND   tRT1.RaceNumber=tRT2.RaceNumber
                          WHERE  (tRT1.RaceFinishTime<tRT2.RaceFinishTime)
                            AND  (DateAdd('n',10,tRT1.RaceFinishTime)>=tRT2.RaceFinishTime))

NB. If you don't want to suffer from any awkward rounding issues with timings, and comparisons thereof, I recommend you use DateAdd(), as I have in my example, rather than DateDiff().