Random non-repeating queries MS Access

[tgassmann]

Hello,

I have a MS access database with 2100 rows of data. Each row has a
unique id called orgid.
Each row also has a numeric field called orgtype. Currently, there are
9 different orgtypes.

I would like a random non-repeating query that will select X number of
rows from each orgtype and copy all DB fields into a new DB table.

What I mean by non-repeating, when a organization is selected, it is
marked, so that it can never be selected again.

This is a time sensitive school project.
I am not very good at programming in MS Access, but I am a quick
learner.
Any help would be appreciated.

Thanks
Jlimited

 

[John Vinson]

Hello,

I have a MS access database with 2100 rows of data. Each row has a
unique id called orgid.
Each row also has a numeric field called orgtype. Currently, there are
9 different orgtypes.

I would like a random non-repeating query that will select X number of
rows from each orgtype and copy all DB fields into a new DB table.

What I mean by non-repeating, when a organization is selected, it is
marked, so that it can never be selected again.

You can use the Top Values property of a query, with help
from a little VBA. 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.

To exclude records which have already been selected, join the target
table to this query using a Left Outer Join, and put a criterion of IS
NULL on the joined table field.

John W. Vinson[MVP]