Solved

List containing two columns need random assignment of values

Posted on 2013-05-17
14
457 Views
Last Modified: 2013-05-19
I have a table with two columns:

Column1        Column2
Someplace1   596
Someplace2   389
Someplace3   196
.
.
.
Someplace96  983

I need to assign the values in column 2 randomly against column 1.

To clearify: I need to randomly assign each value in column 2 to the original order of values in column 1. Any suggesions or script examples would be very helpful.
0
Comment
Question by:EvertJor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 100 total points
ID: 39176825
This works in tests on a small table. It assumes that there is no zero in the numbers.
Sub Reorder()
Dim rs As New ADODB.Recordset
Dim lNumbers() As Long
Dim r As Integer
Dim iRecordCount As Integer

rs.Open "randomise", CurrentProject.Connection, adOpenDynamic, adLockPessimistic

'count records and resize array
rs.MoveFirst
Do Until rs.EOF
    iRecordCount = iRecordCount + 1
    rs.MoveNext
Loop
ReDim lNumbers(iRecordCount - 1)

'put numbers into array in random order
Randomize
rs.MoveFirst
Do Until rs.EOF
    Do
        r = Int(Rnd * iRecordCount)
    Loop Until lNumbers(r) = 0
    lNumbers(r) = rs.Fields("Numbers").Value
    rs.MoveNext
Loop

'copy numbers from array to table
rs.MoveFirst
r = 0
Do Until rs.EOF
    rs.Fields("Numbers").Value = lNumbers(r)
    rs.Update
    rs.MoveNext
    r = r + 1
Loop

End Sub

Open in new window

0
 
LVL 45

Accepted Solution

by:
aikimark earned 400 total points
ID: 39177229
@Graham

1. You shouldn't have to iterate the recordset just to get the number of records.  You can use the recordset variable's .RecordCount property.  Better yet, use the RecordCount property of the tabledef.
Example:
iRecordCount = dbEngine(0)(0).TableDefs("randomise").RecordCount
ReDim lNumbers(iRecordCount - 1)

Open in new window


==================
Rather than assume there are no zeroes and no duplicate values, you would be better off approaching this like a card deck shuffle.  In this approach, you place all the column 2 values into a collection, dictionary, or array and then pick a (pseudo-random) item from the list and delete that item. Repeat until there are no more items.

This approach would be a better performer, since you aren't worried about duplicate pseudo-random numbers.  There are other problems with VB's PRNG that I've analyzed in this article:
http:A_11114-An-Examination-of-Visual-Basic's-Random-Number-Generation.html

In this example, I'm using a collection, since adding/removing items is so simple.
Sub Q_28132078()
    Dim rs As Recordset
    Dim lngRnd As Long
    Dim colValues As New Collection
    
    Set rs = DBEngine(0)(0).OpenRecordset("randomize", dbOpenDynaset)  'use your table name
    
    'add column2 values to the collection
    Do Until rs.EOF
        colValues.Add rs.Fields("Column2").Value
        rs.MoveNext
    Loop
    
    Randomize
    
    'assign numbers from collection back to table
    rs.MoveFirst
    Do Until rs.EOF
        lngRnd = Int(Rnd * colValues.Count) + 1     'value between 1 and colvalues.count
        rs.Edit     'Update the current record with a random value
            rs.Fields("Column2").Value = colValues(lngRnd)
        rs.Update
        colValues.Remove lngRnd
        rs.MoveNext
    Loop
    
End Sub

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39177569
Aikmark,
Thank you for that.
I was using ADO, it being a later technology, so the TableDef property isn't available. As you will know, the ADO RecordCount property isn't reliable. MoveLast doesn't help, so walking though the records seems to be the next best thing

From the question, assuming that zero is not a legal number seems reasonable. If it turns out that 0 is an allowbale value then pre-filling the array with a defined illegal number, such as -1 would work just as well.

EvertJor,
Please be prompt in responding to comments, otherwise you are liable to get quibbles like this between experts, which might be the source of some confusion.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 45

Expert Comment

by:aikimark
ID: 39177660
I usually instantiate recordsets from the dbEngine(0)(0) object when I'm inside of an Access routine and querying inside the same database.  If forced to use ADO to touch an external table, you can use a "Select Count(*) From tablename" query to ascertain the number of rows.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39177760
EvertJor,

There is nothing wrong with aikimarks's contribution. It is just like goimg left, then right, as opposed to going right, then left to get to the opposite corner from the current position.

If you are confused then, as I said before, be advised to attend to your questions as soon as possible
0
 

Author Comment

by:EvertJor
ID: 39177763
Compliments to both of you for your answers!
Actually aikimark -  it would be a problem with the data if there are duplicates and this can't be recognized....

One example would be that a new and an old part are registered in the same table with the same value. So two identical records... If one of the parts is defective and both were scanned with an x-ray machine (costly) to detect this (and it wasn't entered in the db it would be a shame not to carefully avoid the problem with identical entries ;-))
Someone pulled off the sticker saying which part was the new one...

I'm testing both solutions. Thanks again!
0
 

Author Comment

by:EvertJor
ID: 39177778
So the field is actually a text field and not a number.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39177784
Nevertheless, is the field restricted to a numerical value?
0
 

Author Comment

by:EvertJor
ID: 39177793
aikimark and GrahamSkan.

I'm getting "User type not defined error" on your VBA code Graham.

aikimark: Your solutions works on a simple dataset (I've not taken the duplicate-problem into account yet). I changed your code slightly to add the random values into an empty column.

Then I need to detect the duplicates beforehand - so before making the new column with the random values we need to check for duplicates and give the user a warning (better safe than...) to abort the subroutine. I could use a query to detect duplicates and then give a warning before running the code?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39177823
What is your environment OS, (Application and version)?

Perhaps your version doesn't automatically connect to ADO?
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 400 total points
ID: 39178097
You can check for duplicate values with a Group By query.
Example:
    Set rs = DBEngine(0)(0).OpenRecordset("Select Column2, Count(Column2) From randomize Group By Column2 Having Count(Column2) > 1", dbOpenDynaset)
If rs.EOF Then
Else
    intReply = Msgbox("Duplicate Column2 values detected. Click Yes to continue.", vbYesNo)
    If intReply = vbNo Then    
        'take abortive action
    End If
End If

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39178198
For my code to work at all, there needs to be a reference (Tools/References in the VBA IDE)  to the Microsoft ActiveX Data Objects library. I thought that it was set as standard in Access these days
0
 

Author Comment

by:EvertJor
ID: 39179553
OK, aikimark. Thanks for your suggestion regarding detecting duplicates. It works.
With regards to the left to right and right to left GrahamSkan - I do have a third column with the position ID.

Example:

Position  Numbers1            Numbers 2 (randomized)
1             Part 1                   Part 3
2             Part 2                   Part 1
3             Part 3                   Part 2
4             Part 4                   Part 4

So I always know where the parts are since I know the position they are in.
The reason for doing it that way is to have two different point to detect systematic errors in analytical systems.

Thanks to both of you for your help.
0
 

Author Closing Comment

by:EvertJor
ID: 39179567
My OS is Windows 8 and Access 2010.

To GrahamSkan: In the 64 bit v. of Access very few things seemed standard to me. I went from 2003 to 2010.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question