Is this by any chance related to this thread705-899700?
If so, this also seems like a quite unnormalized structure. Picture the following table structure, whic I'll play a little with:
[tt]tblRace
ScoutID (startno?)
RunNo
HeatNumber
LaneNo
tmpSortOrder
...[/tt]
Allow for RunNo and ScoutID (startno) to be composite primary key.
This structure will allow you to store all these runs within the same table. I've added (at least) one additional field, to keep the randomized sort order.
Now, since this is a relative small number of contestants, there shouldn't be any danger in doing this through recordset approaches, say first stuffing the race table with the needed number of runs:
[tt]lngNumRace = 3
for lngCounter = 1 to lngNumRace
strsql = "insert into tblRace (ScoutID, RunNo) " & _
"select ScoutID, " & lngCounter & " from tblScouts"
currentdb.execute strsql, dbfailonerror
next lngCounter[/tt]
Then give a randomized sort order, ensuring also the order of entrants/scouts, is randomized.
[tt]set rs = db.openrecordset("tblRace")
with rs
if ((not .bof) and (not .eof then))
randomize
do while not .eof
.fields("tmpSortOrder").value = _
clng((2147483647) * RND + 1)
.update
.movenext
loop
end if
end with[/tt]
Then open per each run, ordere by this sortorder, and assign lanes
[tt]lngNumRace = 3
for lngCounter = 1 to lngNumRace
strsql = "select ScoutID, HeatNumber, LaneNo " & _
"from tblRace " & _
"order by tmpSortOrder"
set rs = db.openrecordset(strsql)
if ((not .bof) and (not .eof then))
lngHeatNumber = 1
lngLane = 1
' you probably need some logic here to take care of what
' happens when there are for instance 8 contestants left,
' then distribute them over two heats?
do while not rs.eof
' Now - how to determine whether a contestant does not
' start in the same lane for more than one run?
' I think, basically one can do that either when assigning
' lanes, or afterwards. Both will contain some challenges
' which I'll leave out;-) but, say you open a second
' recordset, or use a dcount where this entrant has
' the same laneno, then manipulate it?? - but as said
' I'll leave that out here ...
.fields("HeatNumber").value = lngHeatNumber
.fields("LaneNo").value = lngLane
if (lngLane = lngMaxLane) then
lngLane = 1
lngHeatNumber = lngHeatNumber +1
else
lngLane = lngLane +1
end if
.update
.movenext
loop
end if
next lngCounter[/tt]
To test after an initial assignement of lanes, some of the elements could probably include some of the snippets/suggestions below?
Open a recordset testing whether same contestant occurs in the same lane in more than one run.
[tt] strsql = "select r.ScoutID, r.LaneNo, count(r.ScoutID) " & _
"from tblRace r " & _
"where runno <= " lngCounter & _
"group by r.ScoutID, r.laneno " & _
"having count(r.ScoutID) > 1"
set rs = db.openrecordset(strsql)
' loop this thingie, or close after the first, and reopen
' umpteen times - until no equal lane numbers are found???
' One thing to think about using an approach like this, is
' that when altering one lane for one contestant, one will
' alter a lane for another contestant, which also needs
' testing, afterwards ...
' lets say one has assigned value to
lngLane = rs.fields("LaneNo").value
' some of the tests/procedure could perhaps include
' something like this?
lngTmpLane = lngLane
if lngLane = lngMaxLane then
lngLane = 1
else
lngLane = lngLane + 1
end if
' using the "magic number" 99
' this part (if it works at all), is a bit dangerous for
' heats where there are less contestants than lanes. One
' trick, could perhaps be to check the .recordsaffected
' property of the database object after trying to update
' with the magic number, then increase/decrease til one
' is found.
db.execute "update tblRace set LaneNo = 99 " & _
"where LaneNo = " & lngTmpLane & _
" and RunNo " = lngCounter, dbfailonerror
db.execute "update tblRace set LaneNo = " lngtmpLane & _
"where LaneNo = " & lngLane & _
" and RunNo " = lngCounter, dbfailonerror
db.execute "update tblRace set LaneNo = " & lngLane & _
"where LaneNo = 99 " & _
"and RunNo " = lngCounter, dbfailonerror[/tt]
Roy-Vidar
