Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Washington (not DC)
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to nth a table in a query??? (2000 )

    Does anyone know of a way, using a query to nth a table. I want to randomly select a group of records in a large database....say something like 50 out of 1000, but it must be random.

    Any help would be muchly appreciated...

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to nth a table in a query??? (2000 )

    Check out the Knowledge Base article Q210616 <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q210/6/16.ASP?LN=EN-US&SD=gn&FR=0&qry=randomize&rnk=1&src=DHCS_MSPSS_g n_SRCH&SPR=ACC2000>here</A> for an example of this using DAO.

    As an aside, we considered using this same kind of functionality to generate a list of people for DOT mandated drug tests, but decided to place the legal liability on a 3d party application; just a thought.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to nth a table in a query??? (2000 )

    Have a look here 1st

    HelpTalk

    And this site:

    <A target="_blank" HREF=http://www.techbase.iofm.net/tips/access/tip65.htm>TechBase</A>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to nth a table in a query??? (2000 )

    the only way that springs to mind would be to create a proceedure that randomly selects an individual id from the table in question and pops this into an array. Once this array is full of the requisite number of record id's then run a second query to select the data that you want where the id id in the array. somethin along this pseudo code :
    sub GetRandomData
    intRowCount = number of rows in table
    for x = 1 to 50
    generate random number between 0 and intRowCount
    arrayofnumbers=GetRandomID(random number)
    next x
    'array now full of random id's
    create database connection and recordset
    on query like:
    select fields from table where id in arrayofnumbers

    output data, put data in temporary table for reporting, etc
    end sub

    function GetRandomId(random number)
    select top(1) id from table where id = random number
    GetRandomNumber=query result
    end function

    hope this helps, there may well be holes in this as it is 11:15 pm and it has been a long day ! zzzzzzzzzzzzzz

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    Washington (not DC)
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to nth a table in a query??? (2000 )

    Rupert:

    thanks so much, the TechBase was the easier and simplest for what I am doing.
    It works perfectly.

    Thanks again,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •