select random records problem

I use a query to select 5 random records from my db (1285 records in total)

the fields are, Id - first name - surname - dept

I enter Rnd([id]) in a blank field on my query, set the total to 5, set the
sort order asc and run the query.

Works great - every time I run the query I get 5 different members of
staff - which is exactly what I want.

This is used as a monthly draw to award staff a prize

The problem is, every time the database is closed and reopened, it picks the
exact same members of staff , as though in a loop - if the database is left
open it selects 5 different every time.

what am I doing wrong, and more to the point how can I prevent the same
selection being picked every time??

any help would be appreciated

Using Access 97

Steve

I never had an ongoing random pick to do so never run into this problem but I
have a possible solution.

Add a Picked date to your table. Pre-load with a default date one day older
than the X below.

In an update query use Rnd([id]&[Picked]) with criteria of Date() - X
where the x represents how long ago for them to be eligble again. Update the
Picked field with Date().

Then run select query for those picked today.

--
Build a little, test a little.


"steve goodrich" wrote:

I use a query to select 5 random records from my db (1285 records in total)

the fields are, Id - first name - surname - dept

I enter Rnd([id]) in a blank field on my query, set the total to 5, set the
sort order asc and run the query.

Works great - every time I run the query I get 5 different members of
staff - which is exactly what I want.

This is used as a monthly draw to award staff a prize

The problem is, every time the database is closed and reopened, it picks the
exact same members of staff , as though in a loop - if the database is left
open it selects 5 different every time.

what am I doing wrong, and more to the point how can I prevent the same
selection being picked every time??

any help would be appreciated

Using Access 97

Steve

I never had an ongoing random pick to do so never run into this problem but I
have a possible solution.

Add a Picked date to your table. Pre-load with a default date one day older
than the X below.

In an update query use Rnd([id]&[Picked]) with criteria of Date() - X
where the x represents how long ago for them to be eligble again. Update the
Picked field with Date().

Then run select query for those picked today.

--
Build a little, test a little.


"steve goodrich" wrote:

I use a query to select 5 random records from my db (1285 records in total)

the fields are, Id - first name - surname - dept

I enter Rnd([id]) in a blank field on my query, set the total to 5, set the
sort order asc and run the query.

Works great - every time I run the query I get 5 different members of
staff - which is exactly what I want.

This is used as a monthly draw to award staff a prize

The problem is, every time the database is closed and reopened, it picks the
exact same members of staff , as though in a loop - if the database is left
open it selects 5 different every time.

what am I doing wrong, and more to the point how can I prevent the same
selection being picked every time??

any help would be appreciated

Using Access 97

Steve

I'm snowed ...

My knee jerk reaction is that you are sorting exactly the same each
time you start from scratch. You aren't randomizing anything.

But that's only a part of my problem. Where is the "total" to which
you refer? If that's the property on how many records to return then
it explains how it continues to work just fine between starts. When
you run the query it returns 5 records, the next time you tell it to
run it just gives you the next 3 records - it does NOT re-run the
query.

The way to get done what you want is to create a function in a regular
module to randomize on a seed you pass in. That function has test
first that a local static the flag is not set. If Not set then Set it
and calculate and return the value. If it is set just return with the
old value still in play.

The local flag has to be static so that it retains its state. Ergo,
this calculation is performed only once per execution of the query.

You would choose elements to randomize and compare in the query such
that you'd get the desired results.

RandomNumber: RandomizeNumber(YourArgument)

Somewhere in the archives here I'm sure I still have an example of
that code and a query that used it (back in Access 1 or 2!). If you
need it in a hurry you might google on combinations of

"randomize" "Joe Foster" "Access" "Dev Ashish"

If you're unable to find it get back to me and I'll search here.

HTH
--
-Larry-
--

"steve goodrich" wrote in message
...
I use a query to select 5 random records from my db (1285 records in
total)

the fields are, Id - first name - surname - dept

I enter Rnd([id]) in a blank field on my query, set the total to 5,
set the
sort order asc and run the query.

Works great - every time I run the query I get 5 different members
of
staff - which is exactly what I want.

This is used as a monthly draw to award staff a prize

The problem is, every time the database is closed and reopened, it
picks the
exact same members of staff , as though in a loop - if the database
is left
open it selects 5 different every time.

what am I doing wrong, and more to the point how can I prevent the
same
selection being picked every time??

any help would be appreciated

Using Access 97

Steve

Ooops. Should have read ... next 5 records.

--
-Larry-
--

"Larry Daugherty" wrote in
message ...
I'm snowed ...

My knee jerk reaction is that you are sorting exactly the same each
time you start from scratch. You aren't randomizing anything.

But that's only a part of my problem. Where is the "total" to which
you refer? If that's the property on how many records to return
then
it explains how it continues to work just fine between starts. When
you run the query it returns 5 records, the next time you tell it
to
run it just gives you the next 3 records - it does NOT re-run the
query.

The way to get done what you want is to create a function in a
regular
module to randomize on a seed you pass in. That function has test
first that a local static the flag is not set. If Not set then Set
it
and calculate and return the value. If it is set just return with
the
old value still in play.

The local flag has to be static so that it retains its state. Ergo,
this calculation is performed only once per execution of the query.

You would choose elements to randomize and compare in the query such
that you'd get the desired results.

RandomNumber: RandomizeNumber(YourArgument)

Somewhere in the archives here I'm sure I still have an example of
that code and a query that used it (back in Access 1 or 2!). If you
need it in a hurry you might google on combinations of

"randomize" "Joe Foster" "Access" "Dev Ashish"

If you're unable to find it get back to me and I'll search here.

HTH
--
-Larry-
--

"steve goodrich" wrote in message
...
I use a query to select 5 random records from my db (1285 records
in
total)

the fields are, Id - first name - surname - dept

I enter Rnd([id]) in a blank field on my query, set the total to
5,
set the
sort order asc and run the query.

Works great - every time I run the query I get 5 different members
of
staff - which is exactly what I want.

This is used as a monthly draw to award staff a prize

The problem is, every time the database is closed and reopened, it
picks the
exact same members of staff , as though in a loop - if the
database
is left
open it selects 5 different every time.

what am I doing wrong, and more to the point how can I prevent the
same
selection being picked every time??

any help would be appreciated

Using Access 97

Steve

You need to initialize the random or you will get the same exact set of
random numbers every time.

From a posting by John Vinson

See http://support.microsoft.com/default.aspx?id=208855

Possible, not quite as easy as it ought to be.

Copy and paste this little function into a module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);

Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize ' this generates a new start to the random sequence
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces
Access
to give you a new random number for every row. If you don't have a
numeric field available then you can use RndNum(Len([SomeField])) to
force a number to be generated.

Sort by this field and it will shuffle your data into random order.

Source: John Vinson

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


steve goodrich wrote:
I use a query to select 5 random records from my db (1285 records in total)

the fields are, Id - first name - surname - dept

I enter Rnd([id]) in a blank field on my query, set the total to 5, set the
sort order asc and run the query.

Works great - every time I run the query I get 5 different members of
staff - which is exactly what I want.

This is used as a monthly draw to award staff a prize

The problem is, every time the database is closed and reopened, it picks the
exact same members of staff , as though in a loop - if the database is left
open it selects 5 different every time.

what am I doing wrong, and more to the point how can I prevent the same
selection being picked every time??

any help would be appreciated

Using Access 97

Steve

On Thu, 16 Jul 2009 20:52:41 +0100, "steve goodrich"
wrote:

I use a query to select 5 random records from my db (1285 records in total)

the fields are, Id - first name - surname - dept

I enter Rnd([id]) in a blank field on my query, set the total to 5, set the
sort order asc and run the query.

Works great - every time I run the query I get 5 different members of
staff - which is exactly what I want.

This is used as a monthly draw to award staff a prize

The problem is, every time the database is closed and reopened, it picks the
exact same members of staff , as though in a loop - if the database is left
open it selects 5 different every time.

what am I doing wrong, and more to the point how can I prevent the same
selection being picked every time??

any help would be appreciated

Using Access 97

Steve


You can use the Top Values property of a query, with help from a little VBA to
rescramble the random numbers every time it runs. Put this little function
into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.
--

John W. Vinson [MVP]

Thank you so much John - It works perfectly.
Steve
"John Spencer" wrote in message
...
You need to initialize the random or you will get the same exact set of
random numbers every time.

From a posting by John Vinson

See http://support.microsoft.com/default.aspx?id=208855

Possible, not quite as easy as it ought to be.

Copy and paste this little function into a module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);

Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize ' this generates a new start to the random sequence
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces
Access
to give you a new random number for every row. If you don't have a
numeric field available then you can use RndNum(Len([SomeField])) to force
a number to be generated.

Sort by this field and it will shuffle your data into random order.

Source: John Vinson

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


steve goodrich wrote:
I use a query to select 5 random records from my db (1285 records in
total)

the fields are, Id - first name - surname - dept

I enter Rnd([id]) in a blank field on my query, set the total to 5, set
the sort order asc and run the query.

Works great - every time I run the query I get 5 different members of
staff - which is exactly what I want.

This is used as a monthly draw to award staff a prize

The problem is, every time the database is closed and reopened, it picks
the exact same members of staff , as though in a loop - if the database
is left open it selects 5 different every time.

what am I doing wrong, and more to the point how can I prevent the same
selection being picked every time??

any help would be appreciated

Using Access 97

Steve