Random Number generation

  • Thread starter Thread starter Kishor
  • Start date Start date
K

Kishor

Hi,
I am facing a problem in writing a query in access 2000, my query is very
simple.
I wanted to have a random numbers against of every row in a table. So that
whenever I will execute a query I should get different result in every row
in that randomNumber column.

If you have any solution Please let me know.
TIA,
Kishor
 
If your query has a unique numeric field (named "ID" in this example), you
can type this into the Field row of the query design grid:
RandNum: Rnd([ID])

Sometime before running the query (once per session), you would need to
issue a Randomize command.

Access does not do anything with the ID number, but if you don't pass it to
the function, the optimizer figures it does not need to call the function
again every line.
 
Hello Kishor,

The following example will work in the Northwind sample database. You may need to modify
it to suit your needs. I am using an idea given in KB article 208855, where they use a
variable numeric field:

Find N Records in Random Order
http://support.microsoft.com/?id=208855

I chose OrderID, which is an Autonumber (ie. long integer) primary key in the Orders
table. Because I used the primary key, I do not need to test for nulls.

Step 1
Create a new module and name it basRandomNumberGenerator. Copy and paste the following
code into this module. You can specify the lowest and highest values that you want
returned, by adjusting the values of the sngLowerbound and sngUpperbound variables (within
the limits acceptable for a single data type).

Option Compare Database
Option Explicit

Function GenerateRandomNumber( _
lngSeed As Long) As Single

On Error GoTo ProcError

Dim sngLowerbound As Single
Dim sngUpperbound As Single

sngLowerbound = 1 '<--Specify lowest possible number
sngUpperbound = 5000 '<--Specify highest possible number

' Initialize the random-number generator
' with a seed based on the system timer.
Rnd (lngSeed)


GenerateRandomNumber = _
Int((sngUpperbound - sngLowerbound + 1) _
* Rnd + sngLowerbound)

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
, "Error in GenerateRandomNumber event procedure..."
Resume ExitProc
End Function


Step 2
Create a new query by copying & pasting the SQL statement shown below:

SELECT Customers.CompanyName, Orders.OrderDate,
GenerateRandomNumber([OrderID]) AS RandomNum
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;


When you run the query, you should see a calculated column, with random numbers.

Tom

________________________________________


Hi,
I am facing a problem in writing a query in access 2000, my query is very
simple.
I wanted to have a random numbers against of every row in a table. So that
whenever I will execute a query I should get different result in every row
in that randomNumber column.

If you have any solution Please let me know.
TIA,
Kishor
 
Back
Top