SQL in Access97

[Jeff Rush]

Hi all,

I am wondering if there is a way to randomize a maketable query on certain
fields? I can see in the code where they have it taking the TOP 15 PERCENT
and I was wondering if there was a way to change that to RANDOM 15 PERCENT.
I tried it and got a syntax error.

Any help or information pionting me in the right driection would be GREATLY
appreciated.

Thanks,

Jeff

 

[John Vinson]

I am wondering if there is a way to randomize a maketable query on certain
fields? I can see in the code where they have it taking the TOP 15 PERCENT
and I was wondering if there was a way to change that to RANDOM 15 PERCENT.
I tried it and got a syntax error.

Why the MakeTable? You can just use the Select query as the
recordsource for a Form, a Report, an Export, or pretty much anything
else!

To get a random subset of records, 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.

 

[Jeff Rush]

That is going to make my LIFE SO MUCH EASIER!!
Thank you!!

I am wondering if there is a way to randomize a maketable query on certain
fields? I can see in the code where they have it taking the TOP 15 PERCENT
and I was wondering if there was a way to change that to RANDOM 15 PERCENT.
I tried it and got a syntax error.

Why the MakeTable? You can just use the Select query as the
recordsource for a Form, a Report, an Export, or pretty much anything
else!

To get a random subset of records, 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.