Home Page
How
to create a random number in an SQL query on Microsoft Access?
First of all, why
should this be a problem?
Answer: the
problem is that if your field is calculated as Expr1 : Rnd(),
the value of the random number is visited once, and repeated throughout the
query execution. However, if you’d like
a random number unique to each record, here’s how to do it:
- You need an ID or
some other AutoNumber field.
- Once you have an
auto number field, create a new field that will hold the random number. Lets call this new field RN.
- Create an update
query that updates into the RN field the following expression
Expr1: Rnd(Now())+Rnd(Right([ID],3))*100000
- The RN values will
be from 1 to 999999.
- Alternatively, you
can create a query that generates random numbers on the fly, but does not
store them. For example, have an
ID (or other AutoNumber) field set up in a table. Create a regular query with the
following fields:
RandomNumber
:
Rnd(Right([ID],2))
AnotherRandomNumber : Rnd(Right([ID],3))*10
Note that if you want to create additional RN fields (lets call them
RN1, RN2…) all you need to do is change the arithmetic of computing the random
number. However, in order to be effective, there must be a reference to the
value of ID. Because this value changes on every record’s fetch, the
random number re-computes dynamically throughout the execution of the query.
Practical uses:
1.
Generating a random selection for audits.
2.
Creating a simulation with random values in records.
3.
Showing off.
Yigal Rechtman
2003 ©