VBA, random record selector???

random record selector???

Author	Message
Thomas Froh #1 / 4	random record selector??? I have tried this a couple different ways and my results are less than optimistic. I want to select 100 completely random records from a table of 50,000 or so unique records. The results must be different each time I run it. And all 100 of the records must be unique (I cant have record 20 show up twice). I tried creating a random autonumber field in the table itself, sorting and selecting the first 100 but the results are the same each time I remove and re-create the random field. I'm looking for VBA code that will accomplish this. I eventually want a new table created programmatically with 1 field and the 100 random numbers (1-50,000) placed in the table with no duplicates. I then want to run a query on my data to match the records with my random numbers. Anybody have an idea? Tom
Wed, 20 Aug 2003 08:32:05 GMT

Viktor Umanski #2 / 4	random record selector??? Just use Random function to get random numbers. To check for duplicates use Recordset.FindFirst method or establish unique index on new table. Quote: > I have tried this a couple different ways and my results are less than > optimistic. > I want to select 100 completely random records from a table of 50,000 or so > unique records. > The results must be different each time I run it. And all 100 of the records > must be unique (I cant have record 20 show up twice). > I tried creating a random autonumber field in the table itself, sorting and > selecting the first 100 but the results are the same each time I remove and > re-create the random field. I'm looking for vba code that will accomplish > this. > I eventually want a new table created programmatically with 1 field and the > 100 random numbers (1-50,000) placed in the table with no duplicates. I then > want to run a query on my data to match the records with my random numbers. > Anybody have an idea? > Tom
Wed, 20 Aug 2003 12:13:10 GMT

MSherr.. #3 / 4	random record selector??? On Fri, 2 Mar 2001 19:32:05 -0500, "Thomas Frohe" Quote: >I want to select 100 completely random records from a table of 50,000 or so >unique records. Ok. Quote: >The results must be different each time I run it. That's not random. Quote: >And all 100 of the records must be unique (I cant have record 20 show up twice). That's not random, either. Sounds like you want a shuffle algorithm. If you wanted to pick 100 random rows from Northwind's Orders table, you could use something like this: SELECT TOP 100 Orders.OrderID FROM Orders ORDER BY Rnd([OrderID]); -- Mike Sherrill Information Management Systems
Thu, 21 Aug 2003 09:27:03 GMT

david #4 / 4	random record selector??? Quote: > SELECT TOP 100 Orders.OrderID > FROM Orders > ORDER BY Rnd([OrderID]); You have to 'seed' the Rnd function first. Do this by calling 'Randomize' once each time before you run the query. Quote: > >The results must be different each time I run it. > That's not random. Yes it is. Quote: > >And all 100 of the records must be unique (I cant have record 20 show up > That's not random, either. Yes it is. The first assertion is a semantic 'misunderstanding'. The second is either a semantic error or a logic error: sampling without replacement can be either random or non-random: the two factors are orthogonal. Whichever, the query design is definitely clever. Quote: > On Fri, 2 Mar 2001 19:32:05 -0500, "Thomas Frohe" > >I want to select 100 completely random records from a table of 50,000 or so > >unique records. > Ok. > >The results must be different each time I run it. > That's not random. > >And all 100 of the records must be unique (I cant have record 20 show up twice). > That's not random, either. > Sounds like you want a shuffle algorithm. If you wanted to pick 100 > random rows from Northwind's Orders table, you could use something > like this: > SELECT TOP 100 Orders.OrderID > FROM Orders > ORDER BY Rnd([OrderID]); > -- > Mike Sherrill > Information Management Systems
Thu, 21 Aug 2003 11:30:44 GMT

Page 1 of 1

[ 4 post ]