Select random records from recordset / query

[pb21]

I have a recordset based on a union of two queries. From this dataset I want to select a random set of records.

I am assuming its better to do this in VBA rather than the query grid?

Also is it possible to creat a field in the recordset that is not bound to a table, I guess I will need an index field for randomness OR a field to mark to say that that record has been selected randomly so that I dont select the same record twice.

Some pointers would be helpful.

Regards in advance

Peter

 

[mresann]

Off the top of my head, it's simple to do in VBA, but difficult if not impossible, and certainly not practical, to create a query.

I don't have time to create it, but the proper sequence of events in a sub procedure would be:

1. Create two recordset objects; one to clone the existing recordset, one to append the random records to.
2. Get the recordset count.
3. Use the recordset count to establish a random number range.
4. Using the Randomize and Rnd functions, create a group of numbers within the random number range created.
5. Using a sort function (I have one, but there are several on the web; google "Access VBA Sort Function"), return the amount of numbers that equal the amount of random records you want returned.
6. Create a For-Next loop for cycling through the recordset.
7. When the counter variable equals a random number that was returned, add that record to the new recordset.
8. Your new recordset will be filled with random records from the existing recordset.

 

[pb21]

Big thanks

Thank you that will guide me on my way