Randomize ina query - Access 2000

[salvelinus]

I need to select a certain number of records at random from one table and insert them in another. For example, if there are 1000 records in the first table, I want to select 100 of them at random and insert them in another table. I can't think of a way to do this in a query, don't know if it's possible. I'm thinking it could be done via a loop in code, but can it be done directly in a query? Thanks.

[si_the_geek]

It can't be done in a query*. They have no concept of position or order (unless you specifically set one using an Order By clause).

Code is the way to go I'm afraid.



* technically it is possible if you are using a DBMS that supports it, but it would be so awkward and DBMS-centric that code is a much simpler solution.

[salvelinus]

Thanks, si. Trying to help a co-worker, actually. We're a survey firm, and we need to select a random sample of records from data a client sends us, and for statistical purposes it has to be a random set. Their data will either be in Excel or text, not sure. Also, the randomization is only needed on a subset of data, which I don't think is a problem in Access, but I don't know Excel that well (hate it).
The pseudocode I came up with goes roughly (not at work) like this:

'assume a table with 100 records, want 50 randomly selected
Randomize
For i = 1 to 50
   j = Rnd(i * 50) + 1
   strSQl = "INSERT INTO tbl2Foo ...WHERE j = tbl1Foo.fldSomePrimaryKey
   DoCmd.RunSql strsql
Loop

Would this work? Not a huge issue, it's only one client, but would this be really slow for thousands of records?

[si_the_geek]

Excel is fantastic.. it's really easy from VB (compared to lots of other stuff anyway), just see the tutorial link in my sig.


The speed of Insert queries as you have them isn't ideal, as you will already have the data in the database. I would put all the "random" primary keys into a comma-separated string, and do a single Insert at the end via an In clause, eg:

MyInString = MyInString & ", " & [I]randomkey[/I]
   Loop/Next/Whatever!
   strSQL = "INSERT INTO tbl2Foo ...  SELECT .. FROM tbl1Foo WHERE tbl1Foo.fldSomePrimaryKey IN (" & Mid$(MyInString,3) & ")"

In terms of picking the numbers, you need to make sure you get the right amount - in your example you could be duplicating the values of j. What I would do is read all values of fldSomePrimaryKey from the database, and add them to a collection. Use random numbers to pick an element of the collection, then add it to "MyInString" and remove it from the collection (before picking the next).