Random Selection in Access

[J.A.C.]

Does Access have the ability to Gererate a Random list of Numbers like Excel
can? For example I have account numbers in my database and I have to
randomly select several accounts at various times. Presently I export the
list into Excel and use the Data Alalysis tools to do this. I would like to
do this function within Access - can someone help me?

 

[Douglas J. Steele]

Check http://www.mvps.org/access/queries/qry0011.htm at "The Access Web"

 

[J.A.C.]

Okay I did get the code and pasted it in the Mondule section as suggested by
selecting "new"- however when I close it it does not ask "Do you want to save
or Save As" I can only close the modnule. I paste it as:

'Code courtesy of
'Joe Foster
Function Randomizer () As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize : AlreadyDone = True
Randomizer = 0
End Function
Also the second part -
"Now to get 100 questions picked at random:"

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

Exactly where in the query do you enter this information?

Sorry to need so much hand holding - I do appreciate the help.

Thanks

 

[Douglas J. Steele]

All you past into the module is what's in the shaded area (between the Code
Begin and the Code End). Make sure you don't name the module Randomizer
(modules cannot have the same name as subs or functions)

The second part

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

is a sample of what your SQL will look like. If you're not comfortable
working directly with SQL, you can use the query builder as follows:

1. Add your table, then drag whatever fields you want displayed into the
grid.

2. In an empty cell on the Field row, type

Rnd(IsNull(Field1)*0+1)

and select Ascending in the Sort row below that field. Uncheck the "Show"
checkbox under the field. (Replace Field1 with the name of field in your
table. I believe you should choose a field that's part of the primary key).

3. In another empty cell on the Field row, type

Randomizer()

and type 0 as the Criteria below that field. Uncheck the "Show" checkbox
under the field.

 

[J.A.C.]

Thanks - That works well - However - I suspect because this particular table
does not have a primary key(It is being used only to create a random Listing)
I do have some duplicate Account Numbers showing - How can I eliminate these?

 

[Douglas J. Steele]

You'd have to fix the table so that there are no duplicates in it.