asked on

access 2007 randomize

I am trying to create a randomize function in access2007. I would like to run a query to return 100 rows from my table. I would like query to the table an get a different result set everytime i run the query. Help is needed. I am including the database i have started.

aikimark

If you add a column to your query, you can simulate randomness via the VB pseudo-random number function, Rnd(). Pass it the row's key value.
Example:

SELECT ID, Rnd([ID]) AS RndSortValue
FROM MyTable
ORDER BY Rnd([ID]);

Open in new window

Be aware that the VB PRNG functions are flawed. I wrote an analysis in this article:
http:A_11114.html

centralmike

ASKER

The random number columns changes. But it returns the same 100 rows each time you run the query.

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Another similar example:

SELECT TOP 10 tblEmp.EmpID, tblEmp.EmpName
FROM tblEmp
ORDER BY Rnd(Int(Now()*[tblEmp].[EmpID])-Now()*[tblEmp].[EmpID]);

mx

aikimark

@mike

Please post 20 of your ID values. I'm not seeing that behavior with my table/query.

Gustav Brock

You will if you close Access and open it and run the query again.

/gustav

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Guaranteed to not be an issue if you include Now() as shown in my examples.

Gustav Brock

Yes, this is a great tip.

/gustav