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.
centralmikeAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
Here is an example that avoids the issue above - which I've used many times:

SELECT TOP 100 tblEmp.EmpID, tblEmp.EmpName
FROM tblEmp
ORDER BY Rnd(Replace(TimeValue(Now()),":","")*[tblEmp].[EmpID]-Now()*[tblEmp].[EmpID]);
mx
0
 
aikimarkCommented:
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
0
 
centralmikeAuthor Commented:
The random number columns changes.  But it returns the same 100 rows each time you run the query.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Another similar example:

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

mx
0
 
aikimarkCommented:
@mike

Please post 20 of your ID values.  I'm not seeing that behavior with my table/query.
0
 
Gustav BrockCIOCommented:
You will if you close Access and open it and run the query again.

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Guaranteed to not be an issue if you include Now() as shown in my examples.
0
 
Gustav BrockCIOCommented:
Yes, this is a great tip.

/gustav
0
All Courses

From novice to tech pro — start learning today.