SQL simple -> Get random number using Rnd() function

Suppose that we have id column in MS-Access table and want to fill it with a random numbers. It is not difficult to do using VBA function Rnd(). A number that function Rnd() returns is bigger/equal to zero and less than 1. See example:

SELECT Rnd() AS rand_no, Now() AS curr_datetime;

Returns result like this:
rand_nocurr_datetime
0.05623686313625/05/08 09:24:21

Create temp table to store random numbers:
CREATE TABLE temp_tb
(
  idno INT,
  curr_dt DateTime
);

If we want to insert into id column values bigger that 1, we can use following formula: Int([HighNo] - [LowNo] + 1) * Rnd() + [LowNo], where LowNo/HighNo are the edges of the numbers range we chose. Lets say from 1 to 1000.
INSERT INTO temp_tb ( idno, curr_dt )
VALUES (Round (Int(1000-1+1) * Rnd()+1), Now());

After inserting some data temp_tb table will look like this:
idnocurr_dt
79125/05/08 09:59:54
37525/05/08 09:59:58
96325/05/08 10:00:02

Drop table:
DROP TABLE temp_tb;



sqlexamples.info