Random Order of List

[Brandon]

I am creating a database in Microsoft Access to use to manage an event. I am
wanting Access to sort the list randomly from the contestants that are
entered in the query. Then I want it after it randomly sorts them to place
sequential numbers in another field which will be the order they will
participate in the event. Is there a simple way to get access to do this? I
have spent hours trying to figure it out. I can figure out how to assign a
"auto number" and have it random and get a randomly sorted list. But I was
wondering if there was a Macro or something I could use to have it randomly
sort the contestants and then place sequential numbers in another field from
1 thru how many ever contestants are in the query. Thanks in advance for any
help you might be able to give.

 

[Douglas J. Steele]

See whether what's at http://www.mvps.org/access/queries/qry0011.htm helps.

 

[Jae]

Haven't try the code yet. But, this is probably a right direction where you
should work on..

1. Create a field called Random_No And Event_No in your Table

Dim db As Database
Dim rs As Recordset
Dim strSql as string
Dim i as integer

Set db = CurrentDb
Set rs = db.OpenRecordset("Table", dbOpenDynaset)



With rs
Do While Not .EOF

.Edit
!Random_No = Int((60 - 1 + 1) * Rnd + 1) ' would return a
random number between 1 and 60
.Update
.MoveNext

Loop

End With


strSql = "Select * FROM Table ORDER BY R_No;"

Set rs = db.OpenRecordset("strSql", dbOpenDynaset)

For i = 1 to 60 'the number would differ depending on how many events that
your contestants will be entering

With rs
.Edit
!Event_No = i
.Update
.MoveNext

Loop

End With
next i



rs.close
Set db = Nothing
Set rs = Nothing

End Sub

Again.. I haven't tested the code in my db. Let me know if you get an error
or something.

 

[Jae]

It seems that MVPs have answered your question already. Please disregard my
answer.