Using the random function

[Aaron]

Hi all...first post in about a year, but you all (on other access ngs)
were so helpful last time I couldn't imagine not asking for help
again!

I am trying to write a query that populates several lists (of certain
lengths, which I already have produced) with one field that is
randomly generated from a larger list of a very similar type.
Basically, I am predicting future data based on several years of
historical data. I would like the field in question, call it
"Response", to be a random selection from the "Response" field of
those multiple years (in one table).

I am populating a list of about 2,000, from a list of about 16,000, so
I think I can choose random numbers WITH replacement (makes it easier,
right?), but if you would still recommend choosing without
replacement, please let me know how to do that.

At any rate, I want to randomly choose one of the 16,000 Responses
(they are integers) as the entry for each "Response" in the new,
predicted, list of 2,000.

My preferred way of solving this would be to use a query in design
mode. I was hoping I could do this without coding.

For the table names, call the list of 2,000 "2006", and the list of
16,000 "Allyears".

Thank you!

-Aaron

 

[Michel Walsh]

To have a random number, use Rnd().

In a query, that will be 'optimized' as being a 'constant', meaning it is
recognize to NOT depend on any field of any tables. So, it will be computed
only ONCE, at the beginning of the query execution. To have it executed for
each row, use a numeric field as argument: Rnd( fieldName )

To have a pick without duplicated values, that depends on how many out of
many you pick, and if the picking is ordered, or not. As example, to pick
two cards, in order, in a play a 52 cards won't use the same strategy than
to pick the all the 52 cards, in order. In the last case, someone can take a
sequence of 52 random numbers and then, find their ranks: get the order of
picking each card accordingly to the rank value of each random number:

Card RandomValue Rank
1-Spade 0.000456 3
2-Spade 0.956958 51
....

so, the Ace of spade will the be third card to be picked, while the two of
space will be the before last one of the stack. The rank is computed from
the randomValue values. The rank of 3 assumes that 0.000456 is the third
smallest random value in the set of 52 random values we got. The rank of 51
assumes that 0.956958 is the second highest random value in the set.



Hoping it may help,
Vanderghast, Access MVP

 

[Aaron]

To have a random number, use Rnd().

In a query, that will be 'optimized' as being a 'constant', meaning it is
recognize to NOT depend on any field of any tables. So, it will be computed
only ONCE, at the beginning of the query execution. To have it executed for
each row, use a numeric field as argument: Rnd( fieldName )

To have a pick without duplicated values, that depends on how many out of
many you pick, and if the picking is ordered, or not. As example, to pick
two cards, in order, in a play a 52 cards won't use the same strategy than
to pick the all the 52 cards, in order. In the last case, someone can take a
sequence of 52 random numbers and then, find their ranks: get the order of
picking each card accordingly to the rank value of each random number:

Card RandomValue Rank
1-Spade 0.000456 3
2-Spade 0.956958 51
...

so, the Ace of spade will the be third card to be picked, while the two of
space will be the before last one of the stack. The rank is computed from
the randomValue values. The rank of 3 assumes that 0.000456 is the third
smallest random value in the set of 52 random values we got. The rank of 51
assumes that 0.956958 is the second highest random value in the set.

Hoping it may help,
Vanderghast, Access MVP

Thanks.

Hey, if I do succeed in using a query for this, do I need to use the
"Randomize" function in the query? I typed it in, but the query didn't
seem to recognize it (turned it into "Expr1: [Randomize]"

I don't want this query to pick the same numbers every time I open the
database...

-Aaron

 

[Michel Walsh]

Randomize is a statement, not a function. You can use a VBA user defined
function, in a standard module:


Public Function DoRandomize( ) AS long
Randomize
DoRandomize=0
End Function



and, in the query, use:

Expr1: DoRandomize()


It will be recognized as a 'constant' and executed just once (for the
query).


Vanderghast, Access MVP

To have a random number, use Rnd().

In a query, that will be 'optimized' as being a 'constant', meaning it is
recognize to NOT depend on any field of any tables. So, it will be
computed
only ONCE, at the beginning of the query execution. To have it executed
for
each row, use a numeric field as argument: Rnd( fieldName )

To have a pick without duplicated values, that depends on how many out of
many you pick, and if the picking is ordered, or not. As example, to pick
two cards, in order, in a play a 52 cards won't use the same strategy
than
to pick the all the 52 cards, in order. In the last case, someone can
take a
sequence of 52 random numbers and then, find their ranks: get the order
of
picking each card accordingly to the rank value of each random number:

Card RandomValue Rank
1-Spade 0.000456 3
2-Spade 0.956958 51
...

so, the Ace of spade will the be third card to be picked, while the two
of
space will be the before last one of the stack. The rank is computed from
the randomValue values. The rank of 3 assumes that 0.000456 is the third
smallest random value in the set of 52 random values we got. The rank of
51
assumes that 0.956958 is the second highest random value in the set.

Hoping it may help,
Vanderghast, Access MVP

Thanks.

Hey, if I do succeed in using a query for this, do I need to use the
"Randomize" function in the query? I typed it in, but the query didn't
seem to recognize it (turned it into "Expr1: [Randomize]"

I don't want this query to pick the same numbers every time I open the
database...

-Aaron

 

[Aaron]

Randomize is a statement, not a function. You can use a VBA user defined
function, in a standard module:

Public Function DoRandomize( ) AS long
Randomize
DoRandomize=0
End Function

and, in the query, use:

Expr1: DoRandomize()

It will be recognized as a 'constant' and executed just once (for the
query).

Vanderghast, Access MVP

Thanks!

I ended up coding it instead. I'm learning a good bit of VBA, and it
seems to be a much better way of using access in many cases...

-Aaron