MS Access 2002- Pulling Random Number of Records in Query

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

lp092jl

Thread Starter
Joined
Sep 8, 2004
Messages
18
System: Microsoft Windows 2000
Software: Microsoft Access 2002
Subject: MS Access 2002- Pulling Random Number of Records in Query

I am working on pulling a random number or records in an Access query (Top 3 for now) and am trying to figure out how I can include every record from a related table at the same time. I am currently using a randomizer function to pull up 5 random records from one of my tables, the sql for the query reads

SELECT TOP 3 tblOrders.*
FROM tblOrders
WHERE (((randomizer())=0))
ORDER BY Rnd(IsNull(tblOrders.OrderDescription)*0+1);


The function reads:
Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function


I have a customer table (tblCustomers) whose recordset looks like this:
CustomerID FirstName LastName
1 John Atkins
2 Beverly McKinnon
3 Stewart Larsen
4 Debra Svenson
5 Angela Mayor
6 Lewis Hernandez


The related Order table (tblOrders) looks like this:
OrderID CustomerID OrderDescription
1 6 Stationary
2 6 Ballpoint Pens
3 6 Legal Paper
4 6 Lazerjet cartridge-Black
5 1 Notebook pad
6 1 Headset
7 4 3 Ring Binder
8 5 Liquid paper
9 5 Stationary
10 4 Legal Paper
11 2 Lazerjet cartridge-Blue
12 4 Folders
13 4 3 Ring Binder
14 5 Liquid paper
15 5 Ballpoint Pens
16 3 Notebook pad
17 3 Stationary
18 3 Headset
19 2 Lazerjet cartridge-Black











My inquiry is this: How can I create a query that includes all Customers listed in the customer table (tblCustomers) but "randomizes" a set number of related records (3) in the tblOrders table?
 

lp092jl

Thread Starter
Joined
Sep 8, 2004
Messages
18
Hopefully the attached database should clear any confusion.

What I'm hoping to accomplish is a way to include all employees, but just keep the orders randomized in increments of 3.


Please let me know if you can find anything out.
 

Attachments

lp092jl

Thread Starter
Joined
Sep 8, 2004
Messages
18
This attached file has a query that runs random records and includes all Employees, however, its not so easy to upkeep as newer employees will later be added. Is there an easier way to get this query to run in the same manner?
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top