Randomizing data in an Access table column

[PTester]

MS Access

Is it possible to randomize data in one column while keeping the rest of the table intact. I can easily use a query to pull the column and then sort via a random number field, but am having issues putting it back into my table without a relationship.

The database is to keep track of the locks we have placed on lockers in our school. Every semester, I want to randomize the locks in each area so that staff can use a report to move the locks to their new random locations, rather than randomly move the locks on their own and input the new locations into the database. Any help or suggestions would be appreciated.

Thanks,

[PhilOfWalton]

It's a little unclear what you want.

Are you asking for say all the numbers between 1 and 50 in a random order? e.g. 43,20,17, 15,50, 1 .... etc.

Phil

[ADezii]

1. This can actually be done, though it may not be so simple. One Method would be:
   1. Create a Recordset based on your Table.
   2. For a specified number of Iterations, create two Random Record Numbers (Absolute Positions) within the Recordset.
   3. Move to the first Random Position and retrieve the Value in the [LockNum] Field.
   4. Move to the second Random Position and retrieve the Value in the [LockNum] Field.
   5. Swap these two Values and Update the [LockNum] Field in the Recordset.
   6. The more Iterations that you perform, the more Random the exchange since Random Position #1 could equal Random Position #2.
   7. All other Field Values are held, only Locker Numbers are swapped.
2. There may be an easier approach, but none comes to mind right now.
3. Let me know if you are interested and I'll Post the Logic and Code.

[PhilOfWalton]

Exactly what I came up with.

Phil

[PTester]

That sounds great, but also quite complicated. I would greatly appreciate a post of code and logic if you have the time. Thanks to both of you for your replies.

[PhilOfWalton]

Initially I set up a table Numbers as below with Num = NumID just to set some initial values.

Numbers
NumID AutoNumber Key
Num Long No Duplicates
[/code]

Here is the code with the comments that I hope explain what is happening

 
Option Compare Database
Option Explicit
 
Function Unsort(Counter As Long)
 
    Dim MyDb As Database
    Dim NumSet As Recordset
    Dim SQLStg As String
    Dim FirstID As Long
    Dim FirstNo As Integer, LastNo As Integer
    Dim Max As Integer
    Dim i As Integer
 
    SQLStg = "SELECT NumID, Num FROM Numbers;"
 
    Set MyDb = CurrentDb
    Set NumSet = MyDb.OpenRecordset(SQLStg)
 
    With NumSet
        .MoveLast
        Max = .RecordCount                      ' How many numbers
        For i = 1 To Counter                    ' How many pairs do we want to interchange
 
            FirstNo = Int(Rnd * Max)            ' To find which 2 numbers to swap
            If FirstNo <= 1 Then
                FirstNo = 0
            End If
            LastNo = (Rnd * Max)
            If LastNo >= Max Then
                LastNo = Max - 1
            End If
 
            Debug.Print "First: " & FirstNo & "   Last: " & LastNo
            If FirstNo = LastNo Then            ' If numbers are the same, do nothing
                GoTo NextCounter
            End If
 
            .MoveFirst                          ' First record
            .Move FirstNo                       ' Move so many records down
            FirstID = !Numid
            FirstNo = !Num                      ' Save the first number
            .Edit
            !Num = -9999                        ' Change it to a number that will never be used, otherwise we get a duplicate
            .Update
            .MoveFirst                          ' First record
            .Move LastNo                        ' Move so many records down
            LastNo = !Num                       ' Save the second number
            .Edit
            !Num = FirstNo                      ' Change secnd number to first number
            .Update
            .FindFirst "NumID = " & FirstID     ' Find 1st number again
            .Edit
            !Num = LastNo                       ' Change 1st number (-9999) to second number
            .Update
NextCounter:                                    ' Round we go again
        Next i
        .Close
        Set NumSet = Nothing
    End With
 
End Function
 

Phil

[jforbes]

This looked like a lot of fun, so here is what I came up with:

Public Sub doRandomize()
 
    Dim lRecordCount As Long
    Dim lCount As Long
    Dim IDs() As Long
    Dim lSwap As Long
    Dim lTemp As Long
 
    Dim sSQL As String
    Dim oRst As DAO.Recordset
 
    Randomize
    lRecordCount = DCount("ID", "Randomize")
    ReDim IDs(lRecordCount)
 
    ' Get IDs and load them into an Array
    lCount = 1
    sSQL = "SELECT ID FROM Randomize ORDER BY ID"
    Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbDenyWrite)
    Do While Not oRst.EOF
        IDs(lCount) = oRst!ID
        lCount = lCount + 1
        oRst.MoveNext
    Loop
 
    ' Randomize IDs, iterate through the Array and swap it with some other random Array item
    For lCount = 1 To lRecordCount
        lTemp = RoundUp(Rnd * lRecordCount)
        lSwap = IDs(lCount)
        IDs(lCount) = IDs(lTemp)
        IDs(lTemp) = lSwap
    Next lCount
 
    ' Update Records
    lCount = 1
    oRst.MoveFirst
    Do While Not oRst.EOF
        oRst.Edit
            oRst!ID = IDs(lCount)
        oRst.Update
        lCount = lCount + 1
        oRst.MoveNext
    Loop
    oRst.Close
 
End Sub
Public Function RoundUp(ByVal x As Double) As Double
    RoundUp = IIf(x = Int(x), x, Int(x + 1))
End Function
 

Basically, it loads a Column to be randomized into an Array. Then it shuffles the Array. Then writes the shuffled array back into to the Column.

[ADezii]

1. Here is a solution that I came up with. In hindsight, I do not think that it is the optimal solution, but I'll post it anyway.
2. Sample Data with Lock Numbers in sequence:
   

   Last Name    First Name    LockNum
   Cencini    Andrew    100
   Freehafer    Nancy    200
   Giussani    Laura    300
   Hellung-Larsen    Anne    400
   Kotas    Jan    600
   Neipper    Michael    700
   Sergienko    Mariya    800
   Thorpe    Steven    900
   Zare    Robert    1000

3. Sample Code (well commented):
   

   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim intRecCount As Integer
   Dim intPos1 As Integer
   Dim intPos2 As Integer
   Const conLOOPS  As Integer = 10     '10 Iterations of Loop
   Dim intCtr As Integer
   Dim intL1 As Integer
   Dim intL2 As Integer
   Dim brk1 As Variant
    
   Randomize
    
   Set db = CurrentDb
    
   Set rst = db.OpenRecordset("tblLocks", dbOpenDynaset)
    
   rst.MoveLast: rst.MoveFirst         'For an accurate Record Count
   intRecCount = rst.RecordCount       'Record Count contained in intRecCount
    
   With rst
     For intCtr = 1 To conLOOPS                'Loop 10 times, mix em up!
       intPos1 = Int(Rnd * intRecCount) + 1    '1st Random Position
         .Move intPos1 - 1                     'Move to 1st Random Position
         brk1 = rst.Bookmark                   'Set Bookmark to this Position
           intL1 = rst![LockNum]               'Store Lock# of 1st Position in intL1
         .MoveFirst                            'Move to 1st Record in Recordset
       intPos2 = Int(Rnd * intRecCount) + 1    '2nd Random Position
         .Move intPos2 - 1                     'Move to 2nd Random Position
           intL2 = rst![LockNum]               'Store Lock# of 2nd Position in intL2
         .Edit
            rst![LockNum] = intL1              'Replace Lock# in 2nd Position with 1st
         .Update
         rst.Bookmark = brk1                   'Move to the 1st Position via a Bookmark
         .Edit
            rst![LockNum] = intL2              'Replace Lock# in 1st Position with 2nd
         .Update
         .MoveFirst
     Next
   End With
    
   rst.Close
   Set rst = Nothing
    
   DoCmd.OpenTable "tblLocks", acViewNormal, acReadOnly

4. tblLocks after Code Execution:
   

   Last Name    First Name    LockNum
   Cencini    Andrew    900
   Freehafer    Nancy    200
   Giussani    Laura    700
   Hellung-Larsen    Anne    400
   Kotas    Jan    1000
   Neipper    Michael    300
   Sergienko    Mariya    600
   Thorpe    Steven    800
   Zare    Robert    100
    

5. Please forgive the lack of formatting since I simply did not have the time to do so. You can also take a look at the Attachment if you so desire.

[PTester]

Thanks for your help. So far I have had the most success with Phil's solution. One missing factor that I clearly didn't emphasize enough in my original post is that I want the locker numbers to only be swapped within their current area. I tried adding a WHERE clause in Phil's code, but it seems to be ignored in my testing. Below I am going to provide my table layout for tblLocks.
ID (Autonumber)
Lock (String engraved on back of lock)
Combo (combination to open the lock)
Area (1 to 5 signifying different wings in the building)
Location (locker number that the lock is on or needs to be moved to, this is the column that needs to be shuffled)
OLocation (I copy the Location column to this column prior to doing any switching so that I can create a report on where locks need to be moved for the staff that do that work)

Again, any help is greatly appreciated. I am learning from the code provided and have tried hard to make it work on my own. Thanks in advance for any additional help.

Cheers,

[PTester]

Ultimately, I would love to be able to provide a solution where the users click a button on the form and the locks are shuffled within their current areas. Then I will have a report generate showing the lock swaps they need to make, which they can cross off as they make the physical changes in location.

[PhilOfWalton]

The very simple way (Not the best) is to add an initial single digit in front of the Lock Number to represent each of your 5 Locations.

The code remains the same, but your reports need the Left$(LockNo,1) for the Location, and the Mid$(LockNo,2) for the lock no.

The better way, and probably easier to set up, is to create a Location Table with LocationID & Location and add a LocationID column to Locks Table.

The code would be similar, but you would in addition, need to save and swap the LocationIDs.

Thinking about it, the second option is much better as you can process one location at a time.

Phil