1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Access 2002- Pulling Random Number of Records in Query

Discussion in 'Business Applications' started by lp092jl, Sep 8, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. lp092jl

    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?
     
  2. lp092jl

    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.
     

    Attached Files:

  3. lp092jl

    lp092jl Thread Starter

    Joined:
    Sep 8, 2004
    Messages:
    18
    Is there any possible suggestion that I can try out?
     
  4. lp092jl

    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?
     

    Attached Files:

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/271790

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice