Random records

[Dave]

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

 

[Allen Browne]

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.

 

[Dave]

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:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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?

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

THanks

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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:
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.

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.