Putting random numbers in data (looping)

[Lanason]

I want to randomly select a number of records from my dataset - (to perform random Stock Checking)

Say 50 records each time - but randomly

I thought about putting a random number in each record and then selecting top top 50 records after sorting in order by the random number.

Q:- how do I write a loop to look at each record - something like below ???

[test 2] = Rnd
For x = 1 To Count <---- this is wrong !!
DoCmd.GoToRecord , , acNext
[test 2] = Rnd
Next

 

[highandwild]

Create a public function that returns the random number.

The function can be called from the update line of an update query.

 

[Lanason]

Thank you, but how do I do that ?

Public function ???
I know how to do an update Query, but what's the syntax for the query ??

 

[highandwild]

See the attached database.

Look at the Query1 query and the Module1 module.

The query calls the RandomNumber function in the module
which returns a random number between 1 and 1000 to three decimal points. This number updates the RandomNumber field in the table.

The inclusion of the field name being passed to the function just ensures that each record gets a different random number. Remove the field name, run the query and see what happens.

This probably generates a unique number over all of the records but that is not guarenteed. If this causes a problem in that the top x query you use produces more than the number of records you need just let me know and I will tweek it.

The other query just shows you that it has worked.

 

[raskew]

Hi -

Take a look at the RandLott02 function in this post.

See if it might help.

Best wishes - Bob

 

[Lanason]

i got it to put in random numbers into the table using 1 to 5 loop
but dont know how to do it when i dont know how many records i have

i need a loop that starts at record 1 then stops at the last record without adding new records

its really the looping bit that i'm stuck with not the random number entry

 

[highandwild]

I'm not sure where the 1 to 5 loop come in.

Do you need to update every record in the table with the random number?

The query will, in effect, do the looping for you as it will start at record 1 and go through all of the records and end at the last record. It will not add a record into the table.

DCOUNT("[YourFieldName]","YourTableName","") will give you the number of records in the table but I'm not sure where you will need this.

 

[darwin25]

Try this


SELECT TOP 50 Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.ReorderLevel
FROM Products
ORDER BY Rnd([ProductID]);


The idea is to display the records randomly by using the Rnd() on the integer primary key

This is a much simpler solution. I hope it's what you are looking for.