Generating a Random Number

[Eric]

Hi,
I am trying to assign a random number to each record so that each record
will be assigned a specific attribute based on a cumulative distribution of
historical data. While designing the make table query I start a new field
that reads as follows:
RandomNumber: Rnd()
The problem is that this function assigns the same random number to each
record (making the cumulative distribution useless!). I was hoping that
Access could assign a random number to each individual record. Is this
possible? If so, how? Thanks.

 

[Brendan Reynolds]

When you call a function from a query, if you don't pass any of the values
from the columns of the query as arguments to the function, the JET database
engine will determine that the result does not depend on the data and
therefore optimise the query by calling the function only once each time the
query is executed, instead of calling it for every row. To force the
function to be called for every row, pass the value of one of the columns as
an argument to the function. You don't have to do anything with the value in
the function, just pass it.

With that in mind, try something like this. In a standard module ...

Public Function RandomNumber(ByVal SomeFieldValue As Variant) As Double
Randomize Timer
RandomNumber = Rnd
End Function

In a query ...

SELECT RandomNumber([SomeFieldName]) AS RandomNumber, * FROM SomeTable;

 

[John Vinson]

Hi,
I am trying to assign a random number to each record so that each record
will be assigned a specific attribute based on a cumulative distribution of
historical data. While designing the make table query I start a new field
that reads as follows:
RandomNumber: Rnd()
The problem is that this function assigns the same random number to each
record (making the cumulative distribution useless!). I was hoping that
Access could assign a random number to each individual record. Is this
possible? If so, how? Thanks.

It requires a bit of tweaking. If you call any function - even Rnd() -
Access will "save time" by just calling it once, unless you pass a
field value as an argument.

Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then insert RndNum([fieldname]) rather than Rnd(), where [fieldname]
is any field in your table - this forces Access to give a different
random number for each record.

John W. Vinson[MVP]

 

[Guy Normandeau]

John,

How do you prevent the function from regenerating the number once assinged?

Hi,
I am trying to assign a random number to each record so that each record
will be assigned a specific attribute based on a cumulative distribution of
historical data. While designing the make table query I start a new field
that reads as follows:
RandomNumber: Rnd()
The problem is that this function assigns the same random number to each
record (making the cumulative distribution useless!). I was hoping that
Access could assign a random number to each individual record. Is this
possible? If so, how? Thanks.

It requires a bit of tweaking. If you call any function - even Rnd() -
Access will "save time" by just calling it once, unless you pass a
field value as an argument.

Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then insert RndNum([fieldname]) rather than Rnd(), where [fieldname]
is any field in your table - this forces Access to give a different
random number for each record.

John W. Vinson[MVP]

 

[Arvin Meyer [MVP]]

The key word "Randomize" in the code is a seed that uses the computer's
timer.

You can also use an autonumber, with it's type set to Random.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

John,

How do you prevent the function from regenerating the number once
assinged?

Hi,
I am trying to assign a random number to each record so that each record
will be assigned a specific attribute based on a cumulative distribution
of
historical data. While designing the make table query I start a new
field
that reads as follows:
RandomNumber: Rnd()
The problem is that this function assigns the same random number to each
record (making the cumulative distribution useless!). I was hoping that
Access could assign a random number to each individual record. Is this
possible? If so, how? Thanks.

It requires a bit of tweaking. If you call any function - even Rnd() -
Access will "save time" by just calling it once, unless you pass a
field value as an argument.

Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then insert RndNum([fieldname]) rather than Rnd(), where [fieldname]
is any field in your table - this forces Access to give a different
random number for each record.

John W. Vinson[MVP]