By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Randomizing data in an Access table column

P: 4
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,
Nov 22 '16 #1
Share this Question
Share on Google+
10 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Nov 22 '16 #2

ADezii
Expert 5K+
P: 8,607
  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.
Nov 22 '16 #3

PhilOfWalton
Expert 100+
P: 1,430
Exactly what I came up with.

Phil
Nov 22 '16 #4

P: 4
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.
Nov 22 '16 #5

PhilOfWalton
Expert 100+
P: 1,430
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Option Explicit
  4.  
  5. Function Unsort(Counter As Long)
  6.  
  7.     Dim MyDb As Database
  8.     Dim NumSet As Recordset
  9.     Dim SQLStg As String
  10.     Dim FirstID As Long
  11.     Dim FirstNo As Integer, LastNo As Integer
  12.     Dim Max As Integer
  13.     Dim i As Integer
  14.  
  15.     SQLStg = "SELECT NumID, Num FROM Numbers;"
  16.  
  17.     Set MyDb = CurrentDb
  18.     Set NumSet = MyDb.OpenRecordset(SQLStg)
  19.  
  20.     With NumSet
  21.         .MoveLast
  22.         Max = .RecordCount                      ' How many numbers
  23.         For i = 1 To Counter                    ' How many pairs do we want to interchange
  24.  
  25.             FirstNo = Int(Rnd * Max)            ' To find which 2 numbers to swap
  26.             If FirstNo <= 1 Then
  27.                 FirstNo = 0
  28.             End If
  29.             LastNo = (Rnd * Max)
  30.             If LastNo >= Max Then
  31.                 LastNo = Max - 1
  32.             End If
  33.  
  34.             Debug.Print "First: " & FirstNo & "   Last: " & LastNo
  35.             If FirstNo = LastNo Then            ' If numbers are the same, do nothing
  36.                 GoTo NextCounter
  37.             End If
  38.  
  39.             .MoveFirst                          ' First record
  40.             .Move FirstNo                       ' Move so many records down
  41.             FirstID = !Numid
  42.             FirstNo = !Num                      ' Save the first number
  43.             .Edit
  44.             !Num = -9999                        ' Change it to a number that will never be used, otherwise we get a duplicate
  45.             .Update
  46.             .MoveFirst                          ' First record
  47.             .Move LastNo                        ' Move so many records down
  48.             LastNo = !Num                       ' Save the second number
  49.             .Edit
  50.             !Num = FirstNo                      ' Change secnd number to first number
  51.             .Update
  52.             .FindFirst "NumID = " & FirstID     ' Find 1st number again
  53.             .Edit
  54.             !Num = LastNo                       ' Change 1st number (-9999) to second number
  55.             .Update
  56. NextCounter:                                    ' Round we go again
  57.         Next i
  58.         .Close
  59.         Set NumSet = Nothing
  60.     End With
  61.  
  62. End Function
  63.  
Phil
Nov 22 '16 #6

jforbes
Expert 100+
P: 1,107
This looked like a lot of fun, so here is what I came up with:
Expand|Select|Wrap|Line Numbers
  1. Public Sub doRandomize()
  2.  
  3.     Dim lRecordCount As Long
  4.     Dim lCount As Long
  5.     Dim IDs() As Long
  6.     Dim lSwap As Long
  7.     Dim lTemp As Long
  8.  
  9.     Dim sSQL As String
  10.     Dim oRst As DAO.Recordset
  11.  
  12.     Randomize
  13.     lRecordCount = DCount("ID", "Randomize")
  14.     ReDim IDs(lRecordCount)
  15.  
  16.     ' Get IDs and load them into an Array
  17.     lCount = 1
  18.     sSQL = "SELECT ID FROM Randomize ORDER BY ID"
  19.     Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbDenyWrite)
  20.     Do While Not oRst.EOF
  21.         IDs(lCount) = oRst!ID
  22.         lCount = lCount + 1
  23.         oRst.MoveNext
  24.     Loop
  25.  
  26.     ' Randomize IDs, iterate through the Array and swap it with some other random Array item
  27.     For lCount = 1 To lRecordCount
  28.         lTemp = RoundUp(Rnd * lRecordCount)
  29.         lSwap = IDs(lCount)
  30.         IDs(lCount) = IDs(lTemp)
  31.         IDs(lTemp) = lSwap
  32.     Next lCount
  33.  
  34.     ' Update Records
  35.     lCount = 1
  36.     oRst.MoveFirst
  37.     Do While Not oRst.EOF
  38.         oRst.Edit
  39.             oRst!ID = IDs(lCount)
  40.         oRst.Update
  41.         lCount = lCount + 1
  42.         oRst.MoveNext
  43.     Loop
  44.     oRst.Close
  45.  
  46. End Sub
  47. Public Function RoundUp(ByVal x As Double) As Double
  48.     RoundUp = IIf(x = Int(x), x, Int(x + 1))
  49. End Function
  50.  
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.
Nov 23 '16 #7

ADezii
Expert 5K+
P: 8,607
  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:
    Expand|Select|Wrap|Line Numbers
    1. Last Name    First Name    LockNum
    2. Cencini    Andrew    100
    3. Freehafer    Nancy    200
    4. Giussani    Laura    300
    5. Hellung-Larsen    Anne    400
    6. Kotas    Jan    600
    7. Neipper    Michael    700
    8. Sergienko    Mariya    800
    9. Thorpe    Steven    900
    10. Zare    Robert    1000
  3. Sample Code (well commented):
    Expand|Select|Wrap|Line Numbers
    1. Dim db As DAO.Database
    2. Dim rst As DAO.Recordset
    3. Dim intRecCount As Integer
    4. Dim intPos1 As Integer
    5. Dim intPos2 As Integer
    6. Const conLOOPS  As Integer = 10     '10 Iterations of Loop
    7. Dim intCtr As Integer
    8. Dim intL1 As Integer
    9. Dim intL2 As Integer
    10. Dim brk1 As Variant
    11.  
    12. Randomize
    13.  
    14. Set db = CurrentDb
    15.  
    16. Set rst = db.OpenRecordset("tblLocks", dbOpenDynaset)
    17.  
    18. rst.MoveLast: rst.MoveFirst         'For an accurate Record Count
    19. intRecCount = rst.RecordCount       'Record Count contained in intRecCount
    20.  
    21. With rst
    22.   For intCtr = 1 To conLOOPS                'Loop 10 times, mix em up!
    23.     intPos1 = Int(Rnd * intRecCount) + 1    '1st Random Position
    24.       .Move intPos1 - 1                     'Move to 1st Random Position
    25.       brk1 = rst.Bookmark                   'Set Bookmark to this Position
    26.         intL1 = rst![LockNum]               'Store Lock# of 1st Position in intL1
    27.       .MoveFirst                            'Move to 1st Record in Recordset
    28.     intPos2 = Int(Rnd * intRecCount) + 1    '2nd Random Position
    29.       .Move intPos2 - 1                     'Move to 2nd Random Position
    30.         intL2 = rst![LockNum]               'Store Lock# of 2nd Position in intL2
    31.       .Edit
    32.          rst![LockNum] = intL1              'Replace Lock# in 2nd Position with 1st
    33.       .Update
    34.       rst.Bookmark = brk1                   'Move to the 1st Position via a Bookmark
    35.       .Edit
    36.          rst![LockNum] = intL2              'Replace Lock# in 1st Position with 2nd
    37.       .Update
    38.       .MoveFirst
    39.   Next
    40. End With
    41.  
    42. rst.Close
    43. Set rst = Nothing
    44.  
    45. DoCmd.OpenTable "tblLocks", acViewNormal, acReadOnly
  4. tblLocks after Code Execution:
    Expand|Select|Wrap|Line Numbers
    1. Last Name    First Name    LockNum
    2. Cencini    Andrew    900
    3. Freehafer    Nancy    200
    4. Giussani    Laura    700
    5. Hellung-Larsen    Anne    400
    6. Kotas    Jan    1000
    7. Neipper    Michael    300
    8. Sergienko    Mariya    600
    9. Thorpe    Steven    800
    10. Zare    Robert    100
    11.  
  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.
Attached Files
File Type: zip Random Lockers.zip (26.1 KB, 22 views)
Nov 23 '16 #8

P: 4
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,
Nov 30 '16 #9

P: 4
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.
Nov 30 '16 #10

PhilOfWalton
Expert 100+
P: 1,430
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
Nov 30 '16 #11

Post your reply

Sign in to post your reply or Sign up for a free account.