Randome Number, rnd() function 5

[Spyrios]

As I am using rnd(), I'm finding that my numbers are not so random as they first seem. For example, Int(Rnd()*10) gives me a predefined number string through several queries, but not a random number. For example, when I open the database and run the query {which only has this one field Rn:Int(Rnd()*10)} I get the output of 7, and then if I re run the query I get 5, then 5, then 2. Now if I close the database and start the process all over again, I get exactly the same output, 7,5,5,2, etc.

I've tried using rnd() with an ID field, but the same happens, as well as doing something like Rnd(Now())+Rnd(Right([ID],3))*100000 and so on.. Does anyone know how to generate an actual random number sequence?

The reason I'm needing this is it is for a multi-user database (small, only 10 people or so) who have a customer call back list, call their customers, and then update the list. My problem lies in that I don't want the reps starting off in the same position or same record. Otherwise I would have two reps calling the same customer at the same time before they edited the record. Any ideas either way?

 

[SonOfEmidec1100]

Not too sure about access but in vb you use the Randomize statement to reseed the rnd each time the app starts to prevent this.

 

[scriverb]

Here is some code that I use for selecting a random # of records from a table.

Code for a database module: [red]basRandomSelect[/red]

Function RandomPrompt()
RandomPrompt = InputBox("Enter the Number of records to random select.", "Random Select Prompt")
End Function

SQL code for a query: [red]qryRandomSelect[/red]

SELECT TOP 25 *
FROM [blue]YourTableName[/blue] 
ORDER BY Rnd(asc([[blue]autonumber_field[/blue]]));

Code for the OnClick of a forms Command Button

Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs("qryRandomSelect").SQL = "SELECT TOP " & RandomPrompt() & _
    " * FROM [blue]YourTableName[/blue] ORDER BY Rnd(asc([[blue]autonumber_field[/blue]]));"
DoCmd.OpenQuery "qryRandomSelect"
db.CLOSE

Update the above blue code with your table name and the name of the AutoNumber field in your table.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Spyrios]

thanks to both of you.

 

[metalboy]

This works great! your excellent! all of you!

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[educate889]

Bob S.

This is my first try at randomizing anything with Access, so I am playing catch up!

But is there a way to do this inside a query and not use the form approach. What I need to do is randomly select say 5 records from an inventory table that will need to be cycle counted and verified.

I was thinking of some way to make an append qry that add to a table storing the randomly selected parts to be counted that day. Then the next day I would append a new set of products to be counted.

Any ideas, is RND function the right idea or am I missing something easier.

Your help is greatly appreciated!

 

[JonFer]

educate889 - If you have a version of Access where Rnd() can generate a random number for each record (instead of a single number for a query), you just sort by that random number and take the top whatever records. Check the help for Rnd() and see what options it has.

John

http://www.interautomateconsulting.com