Solved

access 2007 randomize

Posted on 2014-07-30
8
253 Views
Last Modified: 2014-08-07
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.
0
Comment
Question by:centralmike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 40230255
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
 

Author Comment

by:centralmike
ID: 40230520
The random number columns changes.  But it returns the same 100 rows each time you run the query.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 40230645
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 40230651
Another similar example:

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

mx
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40230728
@mike

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

Expert Comment

by:Gustav Brock
ID: 40231179
You will if you close Access and open it and run the query again.

/gustav
0
 
LVL 75
ID: 40232252
Guaranteed to not be an issue if you include Now() as shown in my examples.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40232926
Yes, this is a great tip.

/gustav
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question