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:

  1. You need an ID or some other AutoNumber field.
  2. Once you have an auto number field, create a new field that will hold the random number. Lets call this new field RN.
  3. Create an update query that updates into the RN field the following expression

Expr1: Rnd(Now())+Rnd(Right([ID],3))*100000

  1. The RN values will be from 1 to 999999.
  2. 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 ©