I want to generate a randomly generated number field in Microsoft
Access, which will change each time a report or query is used

---

Clarification of Question by baldrickuk-ga on 28 Oct 2002 01:30 PST

What I'm trying to achieve is to produce a report with x number of
records randomly selected each time.  Ideally i should be able to
select how many records I want when the query is run.

Baldrick,

I have a cunning plan! (sorry couldn't resist).

There are two approaches you can take to this problem :

1) Write a query (my method)
2) Write a macro (the method I found by doing a google search for
["randomly select records" access])

Here's the idea behind my method :

a) Append to each record a random number
b) sort by that random number
c) take the first x records

As an example my database has the following table :

table name: "final sample"
fields: divid, permno, idate

divid is a unique identifier (like an autonumber field). This is
important to have because we use it in a wierd manner to generate our
random numbers.

To do this I use the following query :

SELECT TOP 100 [final sample].divID, [final sample].permno, [final
sample].idate
FROM [final sample]
ORDER BY Rnd([DIVID]);

Unfortunately, the 100 is hard coded into the query - ideally you'd
like it to ask you before you run the query. You can't use a parameter
in place of the "100" (I tried it, it didn't work). So your
alternatives are :

1) Manually change the query code each time before you run it
2) Write a macro that uses the RunSQL command and a dialog box to get
the input.

Hopefully this helps - if you want me to go into more technical
details feel free to request clarification.

As I said, there are some sites on the web that give you alternative
methods using VisualBasic code. They aren't as intuitive (in my humble
opinion). But for completeness you might want to check this forum post
:

Randomly Selecting Records
http://www.experts-exchange.com/Databases/MS_Access/Q_10253413.html

Regards

calebu2-ga

Search terms (within Google)
"randomly select records" access

Search terms (within the Access help) :
Rnd
TOP

---

Clarification of Answer by calebu2-ga on 29 Oct 2002 06:32 PST

Baldrick,

Sorry I should have mentioned about using the SQL setting. The main
reason I gave the query to you in that format is because it is a lot
easier to type code as an answer than it is to describe what to put
into each cell on the query design page (short of doing screen grab).
I personally tend to use a combination of the graphical design
interface and the SQL code to get my queries to work. Anyway, glad it
works for you and thanks for the rating.

Regards

calebu2-ga

Thanks, I eventually worked out that I needed to go into SQL from the
design menu and enter the code.  It works a treat and has saved me
loads of work.

calebu2-ga's answer is very good, and should get you what you need.  I
just wanted to offer this comment:

A great resource for Access Database questions is Google Groups.  I
prefer the advanced search option.  I type in the search string, set
the number of search results, and type in a wildcard group value (eg.
"microsoft.public.access.*), and that limits search results to only
Microsoft Access newsgroups.  This is an excellent tool, and the
microsoft newsgroups are an excellent resource.  You'll often find
sample cut-and-paste code, such as calebu2's answer, but without
having to sift through Google's complete hitlist.

Of course it all depends on the search string, but limiting it by
newsgroup is a great tool.

Hope that helps in your future trouble-shooting,
palmerb3

an even easier way than sorting by that random id is to use NEWID()..
NEWID() is a sql function that returns a Microsoft GUID (Globally
Unique IDentifier) I'm not positive that it works in Access, since I'm
a SQL Server guy myself, but it's worth a shot. RND() will often times
not be quite as random as you want, especially when selected as a
column in the query.

SELECT TOP 100
  col1,
  col2,
  col3,
  NEWID() as col4
FROM
  table
ORDER BY
  col4

Guaranteed randomness every time (thanks to the nature of GUID's)

enderwiggin,

Good idea - i'd be interested in knowing how "random" both of the
methods actually are - I'm sure for most applications either works OK.
Unfortunately the NEWID() command doesn't seem to work in MSAccess 97.

From what I can tell, the only way to create a new column that either
autonumbers or randomly numbers is to create a stub table, add that
row and then use the append query instead of an insert query. I never
found an easier way than that (which is frustrating, becauase I'm
forever wanting to add an autonumbered field to my query results in a
single step process.

regards

calebu2-ga