Query Random Number Column Manipulation

[gmontano]

I have a query and have the following expression in a column of the query which generates a random number for each record in the query:



Random: Rnd([WorkID])


Is there a way I can manipulate this so that the random number is a whole number with the max number being the total amount of records the query returned?

Below, you can see a possible outcome. If there were 8 records returned in the query, the random numbers would be 1-8, but in no particular order. Although, the random numbers would need to be unique to the others.

WorkID Random
65 5
35 1
34 3
56 4
85 2


I have seen this bit of code:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

I was wondering if you can set the upper to the count of your records and make the results unique some how.

Thank you for your help.

[weekend00]

you can not get a unique sequent number using RND. if you run RND 100 times and convert it within 1 - 100, 99.99% of chance you get at least one pair of duplicate numbers.

[gmontano]

Thank you, Weekend00.

I was afraid of that.

I appreciate you taking the time to help me.

[ajetrumpet]

g,

weekend is right for sure, but you can do this in other ways. but using the RND() function for an uppbound of the query's dataset inside the sql statement, NO you can't.

[TheShabz]

put the numbers 1-8 in an array. use rnd. delete the picked number from the array. loop.