Help with Arrays 2

[AccessGuruCarl]

I need some help working through this. Not familiar with arrays but I'm sure this is the route I need to go.

I have a table of about 50 scouts.

I have 3 other identical tables called Race1, Race2, Race3
Here is the format of the Race tables.
lngHeatNumber - AutoNum Field -PK
intLane1 - Number Field
...
...
intLane6

Here's what I'm trying to do. I want to populate each lane with a scout id at random, starting at record 1 lane1. Then here's where it's gets tricky... I need to do the same thing to Race tables 2 and 3, but... The scout can not race on the same lane as a previous race.

Someone, please point me the direction I need to go, and as much code example as possible.

Thanks in advance...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[fneily]

Found this nice form. The author lets you use it for free. You select a table, then a field within the table and it will scramble the field randomly. Go to:

http://www.rogersaccesslibrary.com/TableOfContents3.asp#D

and download DataScrambler.zip

So you can randomize the scout table.

Can you clarify something - you have 50 scouts and 6 lanes. So you have 8 heats of 6 scouts and 1 heat with 2 scouts?

 

[AccessGuruCarl]

Hi fneily,

Yes, that would be correct. If all scouts attend! This number could change.
What I do is, I have a registration form with a combo box listing all scouts. As a scout is selected I move his info to a temp Racers table.

I hope this helps explain it a little better. I'll also take a look at the link to scramble data.

Do you think I'm on the right track with using an Array to populate the race tables.

Thanks

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[Remou]

How about:

Sub SelectRace1()
Dim rsScoutsTemp As DAO.Recordset
Dim rsRace1 As DAO.Recordset
Dim db As DAO.Database
Dim lngPick As Long
Dim strSQL

Set db = CurrentDb
Set rsRace1 = db.OpenRecordset("tblRace1")

strSQL = "UPDATE tblScoutsTemp SET Raced = FALSE"
db.Execute strSQL

Do While True
    rsRace1.AddNew
    For i = 1 To 6
        strSQL = "SELECT tblScoutsTemp.ScoutID, " _
               & "tblScoutsTemp.Raced " _
               & "FROM tblScoutsTemp " _
               & "LEFT JOIN tblRace1 " _
               & "ON tblScoutsTemp.ScoutID = tblRace1.intLane" & i _
               & " WHERE tblRace1.intLane" & i & " Is Null " _
               & "And Not Raced"
        Set rsScoutsTemp = db.OpenRecordset(strSQL)
        
        If rsScoutsTemp.EOF Then Exit Sub
       
        rsScoutsTemp.MoveLast
        rsScoutsTemp.MoveFirst
        lngPick = Int((rsScoutsTemp.RecordCount * Rnd) + 1)
        rsScoutsTemp.Move lngPick - 1
        rsRace1("intLane" & i) = rsScoutsTemp!ScoutID
        
        rsScoutsTemp.Edit
        rsScoutsTemp!raced = True
        rsScoutsTemp.Update
        
        rsScoutsTemp.Close
    Next
    rsRace1.Update
Loop

Set rsScoutsTemp = Nothing
rsRace1.Close
Set rsRace1 = Nothing
Set db = Nothing

End Sub

 

[RoyVidar]

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

 

[Remou]

Oops.
Intead of:
[tt] If rsScoutsTemp.EOF Then Exit Sub[/tt]

Read:

        If rsScoutsTemp.EOF Then
            rsRace1.Update
            Exit Sub
        End If

 

[Remou]

And race2

Sub SelectRace2()
Dim rsScoutsTemp As DAO.Recordset
Dim rsRace2 As DAO.Recordset
Dim db As DAO.Database
Dim lngPick As Long
Dim strSQL

Set db = CurrentDb
Set rsRace2 = db.OpenRecordset("tblRace2")

strSQL = "UPDATE tblScoutsTemp SET Raced = FALSE"
db.Execute strSQL

Do While True
    rsRace2.AddNew
    For i = 1 To 6
        strSQL = "SELECT tblScoutsTemp.ScoutID, " _
               & "tblScoutsTemp.Raced " _
               & "FROM (tblScoutsTemp " _
               & "LEFT JOIN tblRace1 " _
               & "ON tblScoutsTemp.ScoutID = tblRace1.intLane" & i _
               & ") LEFT JOIN tblRace2 " _
               & "ON tblScoutsTemp.ScoutID = tblRace2.intLane" & i _
               & " WHERE (((tblRace1.intLane" & i & ") Is Null) " _
               & "AND ((tblRace2.intLane" & i & ") Is Null)) " _
               & "AND Not Raced"
        Set rsScoutsTemp = db.OpenRecordset(strSQL)
        
        If rsScoutsTemp.EOF Then
            rsRace2.Update
            Exit Sub
        End If
        
        rsScoutsTemp.MoveLast
        rsScoutsTemp.MoveFirst
        
        lngPick = Int((rsScoutsTemp.RecordCount * Rnd) + 1)
        rsScoutsTemp.Move lngPick - 1
        rsRace2("intLane" & i) = rsScoutsTemp!ScoutID
            
        strSQL = "UPDATE tblScoutsTemp SET Raced=True WHERE ScoutID=" _
            & rsScoutsTemp!ScoutID
        db.Execute strSQL
    
        rsScoutsTemp.Close
    Next
    rsRace2.Update
Loop

Set rsScoutsTemp = Nothing
rsRace2.Close
Set rsRace1 = Nothing
Set db = Nothing

End Sub

 

[AccessGuruCarl]

Thanks for all the posts...

I'll begin testing...

Hi Roy,
Yes it is a bit un-normalized, but this was the only senerio that worked so that I can run an update query to get all 3 tables into a final race table which includes a time field.

So the final table is like such..
txtRaceNum,txtHeatNum,txtLaneNum,intScoutID,timTime

1 1 1 23
1 1 2 34
2 1 1 56
3 1 1 34
3 2 1 34

It's not exactly what I wanted, but I got it to work a few years ago, and I left it at that since we were manually entering the times. But there are to many errors being created when users register and select their lanes.

So I thought I'd make it nice and simple..
Pick the Scout, Enter the Car Weight, and Click Finish!
Then just before race, build the tables...

My ideal final race table i would like in this format, but I don't know how to reference the ScoutID

Ideal Final Table..or Form View
RaceNum,HeatNum,Lane1,Lane2...Lane6
RaceNum hold the Race Number
HeatNum holds the heat number
Lanes1 - 6 holds the time, referenced to a scout id
These fields would be blank, at form opening. And would populate as each heat ran.

But I'm lost....
Playing around with several queries...

I'll get this post working, then work on other issues.

Thanks Again...
I'm sure I'll have some questions, I don't get to program as often as I'd like to due to job changes....

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[MajP]

Carl,
I went last year to my daughters Powder Puff Derby for the Girl Scouts, but the event was sponsored by the Boy Scouts. They had a commercial software that did this and a whole bunch of other things. As the cars came down the track they tripped a sensor. Instantly it calculated and projected on the screen Finish results, Velocity, Time, Heat Standings etc. I think it also did all of the Heat and lane assignments. It was definately a little more hightech than when we were kids. So although you may know this already, I googled and found that there are a ton of companies that make this software. It looked to me like there is some freeware versions out there, but I did not dig real deep.

 

[MajP]

Okay, now I realize that this is big buisiness. People are prettty passionate about this. Here is the freeware that I saw. Looks like everything else is $60.00

http://pack146.nova.org/pinewood/software.html

 

[AccessGuruCarl]

MajP,

Thanks for the reply...

Yes, I'm familiar with some of the software you mention.
As far as the freeware, I found nothing that suited our needs or would allow custom imports or reports so I started building my own when I realized I only needed to add the MSComm Control, and open the port for reading.

When I joined the Pack, they tracked this in excel. And our timer was just updated to accept electronic transfer, so I threw something together in access that records the times automatically like you mentioned in your post.

+++++

Roy Vidar
Yes Roy, This post is related to thread705-899700 which I'm sure is archived by now. That post was in regards to the timer. What a memory you have...
I wouldn't ever want to run up against you in a poker room! LOL...

Thanks Again...


AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[RoyVidar]

I'm terrible at card games, but I sometime work a little with result processing in motorsport, which is probably why I remembered ;-)

Roy-Vidar

 

[AccessGuruCarl]

Everyone,
Thanks again for the posts...

I now have this working without user input.

Roy...
Great coding... and comments!!!
This is exactly how my final table layout is!
Code worked with a few minor changes, but I was recieving undesirable results, when I ran my race query(Race Form).
RunNo and LaneNo not matching up?
FYI - First 3 fields were set to Sort Ascending.
For example.
The first 6 records of the query should be something like this..
Race# Heat# Lane# Car#(ScoutID) Time
1 1 1 23
1 1 2 45
...
1 1 6 10
Next 6 records..
1 2 1 12
1 2 2 17
....
1 2 6 22
With Finally having all records ...
2 1 1 56
3 1 1 53

If you notice why this is occuring, your results... please post update.

Otherwise,
Remou post worked with a little tweaking.
First I populate the 3 tables, then run an append query to append the data to the race table.

Roy,
As for the duplicate lane checking, I was thinking of opening the table and yes, basically chugging through it to find a duplicate. My question here is; If I find one, do I delete and start over?
Maybe I'll let this pass for now! And we'll edit our announcement so that a duplicate lane has been accepted.

Here's my final Issue...
Roy, Based on the final table
Fields as follows: Race#,Heat#,Lane#,ScoutId,Time
Is it possible to display this data on an updatable form in this format.
Race# - header section
Heat#,Lane1,Lane2,Lane3,Lane4,Lane5,Lane6 -details section
Where the lanes would be the Time field, which are currently holding the ScoutID.
Basically, This displaying 6 records at a time in Form View, Is this possible?

Thanks Again.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[RoyVidar]

My problem when just typing into the reply window, is that that I usually miss part of the logic, the first question, I think is a problem with the recordset retreived when assigning heats, try changing from

[tt] strsql = "select ScoutID, HeatNumber, LaneNo " & _
"from tblRace " & _
"order by tmpSortOrder"[/tt]

to

[tt] strsql = "select ScoutID, HeatNumber, LaneNo " & _
"where RunNo = " & lngCounter & _
"from tblRace " & _
"order by tmpSortOrder"[/tt]

Disallowing duplicate lanes isn't something I've worked with previously, and it is perhaps a bit more involved than just switching lanes within the heat, as I suggested. I haven't had a chance to look seriously at it, but I think the following query should list entrants with dupe lanes (using ADO and getstring to ouput to immediate pane)
[tt]
Dim rs As ADODB.Recordset

strSql = "SELECT t1.ScoutID, t1.LaneNo AS lane1, " & _
"t2.LaneNo AS lane2, t3.LaneNo AS lane3 " & _
"FROM (tblRace AS t1 INNER JOIN tblRace AS t2 " & _
"ON t1.ScoutID = t2.ScoutID) INNER JOIN tblRace " & _
"AS t3 ON t2.ScoutID = t3.ScoutID " & _
"WHERE (t1.RunNo=1 AND t2.RunNo=2 AND t3.RunNo=3) " & _
"and t1.scoutid in ( " & _
"SELECT tblRace.ScoutID " & _
"FROM tblRace " & _
"GROUP BY tblRace.ScoutID, tblRace.LaneNo " & _
"HAVING Count(tblRace.scoutid) > 1 )"
Set rs = New ADODB.Recordset
With rs
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open strSql, CurrentProject.Connection, , , adCmdText
End With
Debug.Print rs.GetString
rs.Close
Set rs = Nothing[/tt]

This recordset should also be updateable.

What you wish to achieve, is probably doable, but I think I'd placed them in a continuous form below eachother, then just created a report or something to present the results afterwards.

To make something like you wish, you'll probably need to work with self joins similar to the SQL above, I think.

Roy-Vidar