Random records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 09 Nov 2005 04:01 GMT

How can I write a query that will return a list of random records from a
table?

The table has an autonumber value and I want it get a random set of
auto-generated numbers with each query.

THanks

Reply to this Message

Allen Browne - 09 Nov 2005 04:24 GMT

You can get a random sample by sorting the records on a random value like
this:
SELECT TOP 10 Table1.* FROM MyTable ORDER BY Rnd([ID]);

Notes:
1. You must issue a Randomize before running this. Otherwise VBA gives you
the same sequence of random numbers.

2. The Rnd() does not do anything with the primary key value you pass in,
but if you don't pass one, the query optimizer is too clever and doesn't
bother calling the function at every row.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> How can I write a query that will return a list of random records from a
> table?
[quoted text clipped - 3 lines]
>
> THanks

Reply to this Message

Dave - 09 Nov 2005 04:44 GMT

Thanks Allen

That works but I'm not sure I understand what you mean by "You must issue a
Randomize before running this"?

Dave

> You can get a random sample by sorting the records on a random value like
> this:
[quoted text clipped - 15 lines]
>>
>> THanks

Reply to this Message

Allen Browne - 09 Nov 2005 05:05 GMT

Press Ctrl+G to open the Immediate window.
Enter:
Randomize
You get no response when you press enter, it it reseeds the random number
generator. For more info, put the cursor in the word, and press F1.

If you need Access to do that automatically every time you start:
1. Choose the Modules tab of the Database window, and click New.
Access opens a new module.
Paste this in:
Function Init()
Randomize
End Function
Save the module with a name such as Module1.

2. Create a new macro, and choose the RunCode action.
In the lower pane, beside the Function Name argument, enter:
Init()
Save the macro with the name AutoExec

The macro name is important. Access will run this every time your database
opens.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks Allen
>
[quoted text clipped - 20 lines]
>>> The table has an autonumber value and I want it get a random set of
>>> auto-generated numbers with each query.

Reply to this Message