Random List of people

[firebolt]

I have a database of 250 people who I want to print out randomly. I need to assign seats 1-5 to the first person, 6-10 to the second etc. for a concert. This is a lottery type application to make the process of obtaining tickets fairer. Can you provide support for this question??

thanks,
fiebolt

 

[ButchSchweitzer]

You could setup a field and make the data type autonumber and set the new values to random.

If you set the indexed to Yes(no duplicates) you will be able to sort and never have a duplicating number.

Because you already have the table populated copy the structure only to a new table and add the field to the new table then import the data. You should have a different number for each, and you can blaim the machine for the order.

Good Luck

Butch

 

[firebolt]

Yes that will work great, but I was looking for a programming solution that could regenerate random numbers which selects from the records already in the database. This is a simpler solution and should work well. Do you know any other method that could involve some code?

Thanks,
Firebolt

 

[ButchSchweitzer]

You could add a field for yur random number in your table then use a recordset and step through each record and create a random number and store it in the field that you added.

This could take a while to run because you have the potential of duplicate numbers, you will have to test the entire database random field to see if that number has been used and create another if found to have been used.

I think I would still use the new table method above by doing a make table query. That way everything will be automatic, you could incorporate a macro and make it all seamless.

Try them both and see which you like better. I usually try more than one method before I find what I like best.

Good Luck
Butch

 

[firebolt]

Thanks Butch, that makes alot of sense and I will give it a go and see how it works out. I think you're right that creating another table will make the best possible solution.

Firebolt

 

[scriverb]

I have a ready-made randomizer if you want to use it. The following is a routine that I use to just select any number of records from a table in a random order. The table name, starting point, number to be selected are all prompted items. The routine requires the table to have an AutoNumber field and a Yes/No selection field. It is a generic type of Random Selection routine that allows you to use it in many instances.

I am displaying this code because it is useful in and of itself. I will also paste the modified code that fits your ticket assignment situation which includes a few modifications.

Private Sub RandomBtn_Click()
Dim vRandomSelect As Long
Dim vTableCount As Long
Dim vRecordsToFlag As Long
Dim vTableName As String
Dim vIndexFieldName As String
Dim vFlagFieldName As String
Dim vStartRecCounter As Long
Dim I As Long
Dim MyDB As Database
Dim MyRS As Recordset
Set MyDB = CurrentDb
vTableName = InputBox("Enter the name of the table to be random sampled.", , "tblCases")
vIndexFieldName = InputBox("Enter the name of the Autonumber field", , "RecCounter")
vFlagFieldName = InputBox("Enter the name of the Yes/No field to be flagged", , "RandomSelection")
vStartRecCounter = DMin(vIndexFieldName, "tblCases", vIndexFieldName & " > 0")
vTableCount = DCount("*", vTableName)
Set MyRS = MyDB.OpenRecordset(vTableName, dbOpenDynaset)
vRecordsToFlag = CLng(InputBox("How many records are to be randomly selected?", , vTableCount))
Randomize
For I = 1 To vRecordsToFlag Step 1
Random:
    vRandomSelect = CLng(Int(Abs((vTableCount * Rnd) + 1))) + vStartRecCounter
    MyRS.FindFirst vIndexFieldName & " = " & vRandomSelect
    If Not (MyRS.NoMatch) Then
        MyRS.Edit
        If MyRS(vFlagFieldName) = False Then
            MyRS(vFlagFieldName) = True
            MyRS.Update
        Else
            GoTo Random
        End If
    Else
        GoTo Random
    End If
Next I
End Sub

The following is the modified routine that requires you to make sure that your table has an AutoNumber, Yes/No select field, a field called SeatAssignemnt text(20) and RandomOrderSeq Number Long Integer.

SeatAssignment will be updated with the seats assigned.
RandomOrderSeq will indicate the order of the random selection.

Private Sub RandomBtn_Click()
Dim vRandomSelect As Long
Dim vTableCount As Long
Dim vRecordsToFlag As Long
Dim vTableName As String
Dim vIndexFieldName As String
Dim vFlagFieldName As String
Dim vStartRecCounter As Long
Dim I As Long
Dim vSeatLow As Long
Dim vSeatHigh As Long
Dim MyDB As Database
Dim MyRS As Recordset
Set MyDB = CurrentDb
vSeatLow = 1
vSeatHigh = 5
vTableName = InputBox("Enter the name of the table to be random sampled.", , "tblCases&quot
vIndexFieldName = InputBox("Enter the name of the AutoNumber field", , "RecCounter&quot
vFlagFieldName = InputBox("Enter the name of the Yes/No field to be flagged", , "RandomSelection&quot
vStartRecCounter = DMin(vIndexFieldName, "tblCases", vIndexFieldName & " > 0&quot
vTableCount = DCount("*", vTableName)
Set MyRS = MyDB.OpenRecordset(vTableName, dbOpenDynaset)
vRecordsToFlag = CLng(InputBox("How many records are to be randomly selected?", , vTableCount))
Randomize
For I = 1 To vRecordsToFlag Step 1
Random:
vRandomSelect = CLng(Int(Abs((vTableCount * Rnd) + 1))) + vStartRecCounter
MyRS.FindFirst vIndexFieldName & " = " & vRandomSelect
If Not (MyRS.NoMatch) Then
MyRS.Edit
If MyRS(vFlagFieldName) = False Then
MyRS(vFlagFieldName) = True
MyRS("SeatAssignment&quot = "Seats: " & vSeatLow & " - " & vSeatHigh
MyRS("RandomOrderSeq&quot = I
vSeatLow = vSeatLow + 5
vSeatHigh = vSeatHigh + 5
MyRS.Update
Else
GoTo Random
End If
Else
GoTo Random
End If
Next I
End Sub


Try this second set of code and it should work for you.

Bob Scriver

 

[firebolt]

Bob this looks fantastic, I will copy this code to my database and see how it works. I think this will give me the flexibility within my current setup that I'm looking for.

Thanks,
Firebolt