Random Number in Query?

[Greg Strong]

Hello All,

Is it possible to create multiple random numbers in a query where
there are numerous records?

I've created a custom function. When I use it in a query it creates
the same random number for ALL the records. It appears the function is
only getting called once, therefore only one random number is being
generated for all the records. I want a different random number for
each record. Is this possible, or do I have to do it all in code and
add the random number as data to a field in the table?

What I did is below. At least the short version:

Query:

SELECT tblInvCurYr.ID, ReturnRndCGSPer c(1) AS InvCGSPerc FROM
tblInvCurYr;

Random Function in Code:

Function ReturnRndCGSPer c(dblCGSperc As Double)
Randomize 'initialize random number generator
dblCGSperc = (0.8 - 0.6) * Rnd + 0.6
ReturnRndCGSPer c = dblCGSperc
End Function
TIA!

--
Regards,

Greg Strong

[DGB]

Hi Greg,

I found the same as well. Having a different value for dblCGSperc, this
will correct the issue, for example another field that is different for
each row would do the trick. The question remains - why is this the
case? Not really sure! I did notice that the initial values were
different from the final values. What am I talking about (not really
sure sometimes)? I notice that for record 1 the value as 0.63. After
completion it was 0.71. NB: These aren't actually the values is saw,
just an example.

Hope this helps...

Regards,
Dave
Greg Strong wrote:

Hello All,

Is it possible to create multiple random numbers in a query where
there are numerous records?

I've created a custom function. When I use it in a query it creates
the same random number for ALL the records. It appears the function is
only getting called once, therefore only one random number is being
generated for all the records. I want a different random number for
each record. Is this possible, or do I have to do it all in code and
add the random number as data to a field in the table?

What I did is below. At least the short version:

Query:

SELECT tblInvCurYr.ID, ReturnRndCGSPer c(1) AS InvCGSPerc FROM
tblInvCurYr;

Random Function in Code:

Function ReturnRndCGSPer c(dblCGSperc As Double)
Randomize 'initialize random number generator
dblCGSperc = (0.8 - 0.6) * Rnd + 0.6
ReturnRndCGSPer c = dblCGSperc
End Function
TIA!

--
Regards,

Greg Strong

[Thelma Lubkin]

Greg Strong <NoJunk@NoJunk4 U².com> wrote:
: Hello All,

: Is it possible to create multiple random numbers in a query where
: there are numerous records?

: I've created a custom function. When I use it in a query it creates
: the same random number for ALL the records. It appears the function is
: only getting called once, therefore only one random number is being
: generated for all the records. I want a different random number for
: each record. Is this possible, or do I have to do it all in code and
: add the random number as data to a field in the table?

: What I did is below. At least the short version:

: Query:

: SELECT tblInvCurYr.ID, ReturnRndCGSPer c(1) AS InvCGSPerc FROM
: tblInvCurYr;

: Random Function in Code:

: Function ReturnRndCGSPer c(dblCGSperc As Double)
: Randomize 'initialize random number generator

You reintialize the random number generator each time it's
called. Apparently 'randomize' makes the same
initialization each time. Even if that weren't so, you
don't want to initialize your generator each time you
choose a random, so take the randomize step out of the
function and run it once, before you begin your select.
--thelma

: dblCGSperc = (0.8 - 0.6) * Rnd + 0.6
: ReturnRndCGSPer c = dblCGSperc
: End Function
: TIA!

: --
: Regards,

: Greg Strong

[Greg Strong]

On 22 Sep 2005 18:50:13 GMT, Thelma Lubkin <th****@alpha2. csd.uwm.edu>
wrote:

You reintialize the random number generator each time it's
called.
Ok. So why is this wrong?
Apparently 'randomize' makes the same
initialization each time. Even if that weren't so, you
don't want to initialize your generator each time you
choose a random, so take the randomize step out of the
function and run it once, before you begin your select.

Well I want a different random number each time. I passed the ID to the
random function and it created a different number each time.

--
Regards,

Greg Strong

[Thelma Lubkin]

Greg Strong <NoJunk@NoJunk4 U².com> wrote:
: On 22 Sep 2005 18:50:13 GMT, Thelma Lubkin <th****@alpha2. csd.uwm.edu>
: wrote:

:> You reintialize the random number generator each time it's
:> called.

: Ok. So why is this wrong?
If you reintialize each time, you're telling it
to begin at the same place in the randomization
algorithm as it did the time before, so it chooses
the same random number: remember, these aren't truly
'random': they are repeatable computations if you begin
at the same initial conditions--and you're asking it to
restore the initial conditions every time you ask for
a number.

Apparently 'randomize' makes the same

:> initialization each time. Even if that weren't so, you
:> don't want to initialize your generator each time you
:> choose a random, so take the randomize step out of the
:> function and run it once, before you begin your select.

: Well I want a different random number each time. I passed the ID to the
: random function and it created a different number each time.

The 'random function' doesn't include the randomization
step. That step is used to *initialize* the random sequence
that you will generate with repeated calls to the random
function. If you initialize again you reset the
algorithm to again begin at the first random number in
this sequence. There is yet another function
that you can use to reset the entire sequence so that
the random function gets you a different set of numbers.

--thelma
: Regards,

: Greg Strong