Update Query with Rnd (random numbers) question

[ezykiwi]

Hello Everyone,

Im trying to make a query which will allow me to run a query update so that all my listings will get a new random number generated for them,

Ive got it selecting any listing with a ID of >0 (so basically thats all of the listings)

What id like it to do is then assign a random number to the randsort field, (prefer a number like 0.812 or 0.342 etc etc)

To start with ive tried using the Rnd feature, but its updating all the listings with exactly the same number (though granted the number changes each time the query is run)

I see in some of the other comments regarding rnd that you may need to put a randomize statement somewhere,

Im very new to programming in access and was just wondering am I on the right track, and if so where abouts should i declare the randomize code since its in a query?

Thanks for your time and look forward to your replies
Cheers Ezy

 

[TanisAgain]

This SQL picks out 30 random records from a table. This is a make table query.

SELECT TOP 30 tblRandomRecords.ID, tblRandomRecords.FirstName INTO Newtable
FROM tblRandomRecords
ORDER BY Rnd([ID]);

 

[ezykiwi]

Thanks mate,

Im not trying to pull records, im just trying to update the feild in a table... and populate each record with its own random number in the randsort field....

Cheers for the help though
Ezy

 

[Cosmos75]

When you use Rnd() in a query, it is optimized to only call the function only as many times as is needed. So used by itself, Rnd() only gets called once.

If you were to multiple a numeric field by Rnd(), it would only get called once for each unique number. That is to say, for each query record where Rnd() was multiplied by 1, you would get the same number.

The way to get around this is to create a function to generate your random value.

Public Function RndSQL(varField As Variant) As Double
     Randomize (Now())
     RndSQL = Rnd()
End Function

The trick to making this work is to pass a unique value for varField for each record, such as an autonumber or primary key.

Hope that helps!

 

[ezykiwi]

Awesome, thanks alot for your help,

Im just abit unsure about where I put this function, do I make a module or do I put it somewhere differant,

I know where to put the code if it was in say a form, but since its in a query im just abit confused....

Any advice would be awesome,

Cheers Ezy

 

[Cosmos75]

You need to put it in a module, so that it becomes a function you can call from a query.

e.g.

SELECT PK, strString, RndSQL(PK) as RandomNumber
FROM tblData;

Be sure that whatever you pass to the function is unique, whether it is a primary key of a concatenation of fields.

 

[ezykiwi]

Awesome, thanks for that , Ive got it working awesomely now,

Though it keeps coming up with the message boxes when im about to update and id like to get rid of them, but ill put a more specific question for that...

Once again cheers for the help.

Cheers Ezy