Randoms are not random

[jiblankman]

I have a table with a list of people. I am creating a new table including a specific number of people selected "at random" from the first table. Here is the code that I am using:

Dim strSQL As String
Randomize
strSQL = "SELECT TOP " & NumberToPick & " * INTO [Random Selections] FROM [Random Pool] ORDER BY Rnd(count);"
CurrentDB.Execute strSQL

If I open the database and run this routine I get list 1 which always has the same people in it. If I run the program again, I get list 2 which always has the same people in it but are different from list 1.

I have tried placing the Randomize command at different locations within the code. I have also tried "Randomize Timer" instead of "Randomize" and there is no difference.

Any ideas would be greatly appreciated as this is causing some real issues for us. Thanks.

 

[WayneRyan]

J,

Have you tries using just --> Rnd()

Wayne

 

[jiblankman]

Wayne,

Thanks for the suggestion but that actually returns the entire table. Not really sure why.

 

[WayneRyan]

J,

CurrentDb.Execute will not "return" anything.

It is for action queries.

You need a recordset ... I need more information.

Wayne

 

[jiblankman]

I didn't mean to say that it returned anything. I meant that it created a table which contained all of the contents of the original table instead of a smaller set.

I have two tables with the same structures. The first table is a pool of people to select from, the second table is empty until populated. I have not been using recordsets but can populate the table using the code shown earlier. The problem is that the randomization is not random.

I am still new to VBA and Access and do not know if there is a better way to do what I am trying to do. Can you provide more info?

 

[AccessBlaster]

You might find ths post interesting. ConneXionLost did a great job on randomizing 6 decks of cards with a nice list box type interface.

http://www.accessforums.net/access/6-deck-card-shuffling-access-6721-2.html#post25540

Download Cards2000.mdb in post#21


hth

Richard

 

[jiblankman]

Richard,

Thanks for the link, that is an interesting problem with a very fast solution. Unfortunately, it still suffers from the same problem. The first sort (when starting from the ordered deck) always has the 8 of hearts first.

I edited the VBA to insert the "Randomize" command before the query is called. the same results are seen. It appears that the Randomize command does not work in access VBA. I don't think that is correct, but that is what it looks like.

 

[AccessBlaster]

it still suffers from the same problem. The first sort (when starting from the ordered deck) always has the 8 of hearts first.

Did you use the shuffle button?

 

[jiblankman]

Yes, I used the shuffly button. But if you start from the original table each time you open the database, you get the same first shuffle.

This is what is happening in my database. I have to create the pool of employees from several other databases so the table nearly always looks the same. When I select the random employees, I get basically the same list each time.

 

[WayneRyan]

J,

I've seen that problem before, but the Randomize statement has always
corrected it.

Can you attach a sample DB?

Wayne

 

[AccessBlaster]

Try this sample. I got the idea form this post By John W. Vinson[MVP] 3rd post down.


hth
Richard

 

[jiblankman]

Wayne and Richard,

Thanks for the help and suggestions. I changed the way I was approaching the issue and worked it out. Although I am still unsure why the original way was not working. It made no difference whether the randomize statement was used. Weird.

In the end I created another column in the original pool table with a randomseed that was populated with random numbers. The query then copies the top X people sorted by the seed. Works great and is only a few more lines of code.

Now I need to figure out how to append the entire contents of this table to a table in a separate database. Any help on this one would be great. I started with the following:

Set db = OpenDatabase("s:\database\db2.mdb")
'db.Execute "INSERT INTO [TrackingTable] SELECT * FROM [db1].[Created Table]"
Set db = Nothing

Although I was skeptical it worked great. Until a different user tried running this from their computer and got an error message that freezes Access. Anyone have a more useful way to do this?

 

[AccessBlaster]

Glad it worked out.