How do I generate random records in Access 2003?

P

Pearl

I have an existing database in Access 2003 and would like to extract a set of
random records (eg 5000 out of 200000 records). Is this possible with Access?
I currently do not have a primary key but can create one if needed.
 
P

Pearl

Thanks for the link.
However I only have very basic knowledge of Access and have not used Modules
or created macros in Access. Is there a fool proof way of the random
function? Otherwise I'll probably need step by step help in creating this
function.
 
P

pietlinden

Pearl said:
Thanks for the link.
However I only have very basic knowledge of Access and have not used Modules
or created macros in Access. Is there a fool proof way of the random
function? Otherwise I'll probably need step by step help in creating this
function.
Click to expand...

No. No such thing. Fools can screw up anything.

1. Create the function.
=open a new code module.
-paste in the code. (see below).

'************ 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 *************

-compile and save the module. (this is screwy - Access doesn't seem
to like to update the modules list. If you don't see it, close the
database and reopen.)
-create a new query.
-click the SQL button.

paste this in.

select top 100 mytable.*from mytable
where randomizer() = 0
order by rnd(isnull(mytable.question) * 0 + 1)

change instances of "mytable" to the name of your table. (that was
tricky!)
change "question" to the name of a field in your table.

save.

run.

enjoy.
 
P

Pearl

[email protected] said:
No. No such thing. Fools can screw up anything.

1. Create the function.
=open a new code module.
-paste in the code. (see below).

'************ 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 *************

-compile and save the module. (this is screwy - Access doesn't seem
to like to update the modules list. If you don't see it, close the
database and reopen.)
-create a new query.
-click the SQL button.

paste this in.

select top 100 mytable.*from mytable
where randomizer() = 0
order by rnd(isnull(mytable.question) * 0 + 1)
Click to expand...

Done all the above without any problem
but when I tried pasting the above into the SQL window it says 'Syntax error
in FROM clause'
Do I delete what's already in the SQL table or leave it?
 
D

Douglas J. Steele

Piet had a slight typo in his reply: he's missing a space between the
asterisk and the keyword FROM.

If that doesn't solve the problem, post the actual SQL you've got.
 
Top