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

Topic

New Reply

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…

Reply | Quote

Replies

Check out the Knowledge Base article Q210616 here 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.

Reply | Quote

Have a look here 1st

Also">HelpTalk

And this site:

TechBase

Reply | Quote

Rupert:

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

Thanks again,

Reply | Quote

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

Reply | Quote