SQL in Access97

  • Thread starter Thread starter Jeff Rush
  • Start date Start date
J

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
 
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.
Click to expand...

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.
 
That is going to make my LIFE SO MUCH EASIER!!
Thank you!!


John Vinson said:
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.
Click to expand...

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.
Click to expand...
 
Back
Top