Random selection

[TC]

Randomize() ?
^^^

TC

We have recently put into affect random drug testing. Is there was way to
have Access pick 2% of employees monthly to be called in.

Can this be done in a report of some type?

Assuming that you want a random 2% of employees each month, regardless of
whether they have been tested in the previous month, then you need to create a
query to select 2% at random. The Access Web has a page showing you how to
select records at random from a table at
http://www.mvps.org/access/queries/qry0011.htm, except your SQL will ook like:

SELECT TOP 2 PERCENT * FROM tblEmployee
WHERE Randomizer()=0
ORDER BY Rnd([FieldPK]);

--

Jon

www.applecore99.com - Access Tips and Tricks

 

[Rene]

We have recently put into affect random drug testing. Is there was way to have Access pick 2% of employees monthly to be called in.

Can this be done in a report of some type?

 

[JSand42737]

We have recently put into affect random drug testing. Is there was way to
have Access pick 2% of employees monthly to be called in.

Can this be done in a report of some type?

Assuming that you want a random 2% of employees each month, regardless of
whether they have been tested in the previous month, then you need to create a
query to select 2% at random. The Access Web has a page showing you how to
select records at random from a table at
http://www.mvps.org/access/queries/qry0011.htm, except your SQL will ook like:

SELECT TOP 2 PERCENT * FROM tblEmployee
WHERE Randomizer()=0
ORDER BY Rnd([FieldPK]);

 

[TC]

Ok, I hadn't bothered to follow the link. However, why bother with a
seperate function? You could probably call the proper one directly from the
SQL statement. And since the call does not refer to any fields, Jet would
evaluate it only once - not once for every row returned.

Cheers,
TC

Randomize() ?
^^^

TC

Nope. Check out the page that I was referring to on The Access Web - it is a
user-created function that calls the in-built Randomize function.

We have recently put into affect random drug testing. Is there was

way
to

have Access pick 2% of employees monthly to be called in.

Can this be done in a report of some type?

Assuming that you want a random 2% of employees each month, regardless of
whether they have been tested in the previous month, then you need to create a
query to select 2% at random. The Access Web has a page showing you how to
select records at random from a table at
http://www.mvps.org/access/queries/qry0011.htm, except your SQL will

ook
like:

SELECT TOP 2 PERCENT * FROM tblEmployee
WHERE Randomizer()=0
ORDER BY Rnd([FieldPK])

--

Jon

www.applecore99.com - Access Tips and Tricks

 

[JSand42737]

Randomize() ?
^^^

TC

Nope. Check out the page that I was referring to on The Access Web - it is a
user-created function that calls the in-built Randomize function.

We have recently put into affect random drug testing. Is there was way to
have Access pick 2% of employees monthly to be called in.

Can this be done in a report of some type?

Assuming that you want a random 2% of employees each month, regardless of
whether they have been tested in the previous month, then you need to create a
query to select 2% at random. The Access Web has a page showing you how to
select records at random from a table at
http://www.mvps.org/access/queries/qry0011.htm, except your SQL will ook like:

SELECT TOP 2 PERCENT * FROM tblEmployee
WHERE Randomizer()=0
ORDER BY Rnd([FieldPK])

 

[René]

OK I went into the Module clicked on New and copied the text from that web page into the module.

what next? I have a query set up that only has active employee names and status ie active or temp. How do I apply the module to the query or vice versa and where do I find what the SQL says to type the

SELECT TOP 2 PERCENT * FROM tblEmployee
WHERE Randomizer()=0
ORDER BY Rnd([FieldPK])

 

[JSand42737]

OK I went into the Module clicked on New and copied the text from that web
page into the module.

what next? I have a query set up that only has active employee names and
status ie active or temp. How do I apply the module to the query or vice
versa and where do I find what the SQL says to type the

SELECT TOP 2 PERCENT * FROM tblEmployee
WHERE Randomizer()=0
ORDER BY Rnd([FieldPK])

You need to modify the query that you have slightly. Open it in design view,
and in a new field of the query grid, type:

Rnd([PKField])

Where PKField is the numeric Primary Key. Set this to sort ascending. Next, in
the Top Values Box on the Query menubar, where it currently says "All",
overtype with 2%.

Now when you run the query, you should get the required output.

 

[René]

OK didn't do or understand something here.

I'm going to go way back and you interrupt to tell me where I got off track. I appreciate the patience so I can understand what I'm doing.

I opened the dbase, clicked Modules, clicked new, I then copied

'************ Code Begin ***********
'Code courtesy of
'Joe Foster
Function Randomizer () As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize : AlreadyDone = True
Randomizer = 0
End Function
'************ Code End *************
into the General Field directly under "Option Compare Database" I closed this and saved it as "Randomizer"

I then clicked on Query and created a query based on "Active Employee Query" the only fields represented on this query is [Status] and [Full Name] ///should this have been created directly from the [Employee]table? that's a no big to change.


I am still unsure of where to copy the following

SELECT TOP 2 PERCENT * FROM tblEmployee
WHERE Randomizer()=0
ORDER BY Rnd([FieldPK]);

Do I now add a field to the Employee table called [FieldPK]?

Please set me straight

 

[JSand42737]

OK didn't do or understand something here.

I'm going to go way back and you interrupt to tell me where I got off track.
I appreciate the patience so I can understand what I'm doing.

I opened the dbase, clicked Modules, clicked new, I then copied

'************ Code Begin ***********
'Code courtesy of
'Joe Foster
Function Randomizer () As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize : AlreadyDone = True
Randomizer = 0
End Function
'************ Code End *************
into the General Field directly under "Option Compare Database" I closed
this and saved it as "Randomizer"

I then clicked on Query and created a query based on "Active Employee Query"
the only fields represented on this query is [Status] and [Full Name]
///should this have been created directly from the [Employee]table? that's a
no big to change.


I am still unsure of where to copy the following

SELECT TOP 2 PERCENT * FROM tblEmployee
WHERE Randomizer()=0
ORDER BY Rnd([FieldPK]);

Do I now add a field to the Employee table called [FieldPK]?

Please set me straight

Ok

Firstly, you need to change the name of the module that you saved the code into
to something other than the name of the function, so call it "mdlRandom".

In the example I gave, I was assuming that you had a numeric Primary Key field
called "FieldPK" in the table/query (the advantage of ordering by the primary
key is that it always exists). However, it can quite easily be adapted to work
from a text string instead.

Now, you go into the new query that you have started designing.

Add the required fields to the query grid. Then in a new column in the query
grid, type the following in the "Field" cell:

Rnd(Len([Full Name]))

And set this to sort "Ascending".

In another new column in the query grid, type in the name of the function that
you have:

Randomizer()

And in the criteria cell, enter a 0.

Finally, in the "Top Values" drop down on the query design toolbar, type "2%"
to only select the top 2 percent of records.

Now you should be able to run this query and get the answer you want.

 

[René]

Thanks