Random Record Selection

[BamaColtsFan]

I'm sure you follks get tired of hearing this type of question but I am struggling a little with a Random Record Selection query. I've read much of the information here and searched other sites but I am still having a problem with one small part of my query.

Basically, I need to select a random 10 percent of the records found in a table with about 2000 to 2500 records (the record count changes when the data is refreshed). Based on what I have learned so far, I need to use a SELECT TOP query that includes the Rnd() function. I have applied this in two different ways which I will show in a bit.

The method where I use a SELECT TOP 10 Percent statement (posted by MSAccessRookie) selects the correct number of records but it is always the same records every time. This is the query I use:

SELECT TOP 10 PERCENT *
FROM (SELECT Timecards.Random, rnd(Timecards.Random), INT(Timecards.Random*rnd(Timecards.Random))+1, Timecards.Name, Timecards.[People Group ]
FROM Timecards) AS [RandomList2]
ORDER BY 4;

The second method I've used actually produces a random list every time the query runs but requires that I specify number of records I want returned. The number of records needs to be a variable based on 10 percent of the record count in the Timecards table. I actually use two queries to get these results:

First, I select my records using this query;

SELECT TOP 231 Timecards.Random
FROM Timecards
ORDER BY (Rnd(Timecards.Random));


Next, I select the necessary data from the table using this query;

SELECT Timecards.Name, Timecards.[People Group ], Timecards.Random
FROM Timecards
WHERE (((Timecards.Random) In (SELECT RandomSelection.Random FROM RandomSelection)))
ORDER BY Timecards.Name;

Given that the second method is always a different set of records, I think I prefer it over the first but I need to know how to make the number of records variable. I've tried to replace the TOP value with a subquery but Access doesn't seem to like that very much. If I use the Top 10 Percent method, I get the same records every time.

So, with all that said, how do I make the TOP value a variable?

 

[BamaColtsFan]

Gang... I'm sorry... I'm a complete idiot... I just figured out what I was doing wrong! All I need to do is change the RandomSelection query to use the TOP 10 Percent method and all works perfectly! I guess I was over-thinking it...

This is the new query:

SELECT TOP 10 PERCENT Timecards.Random
FROM Timecards
ORDER BY (Rnd(Timecards.Random));

 

[WayneRyan]

Bama,

Aren't you getting the same set of records every time you sample?

If you don't use the Randomize function, you'll have the same starting
point every time you select a set of records.

Wayne

 

[BamaColtsFan]

Nope... I've run the query dozens of times today and I get a different set of records every time. I've seen a lot of postings that suggest this method will give the same set of records but for whatever reason, my query works perfectly every time...

 

[WayneRyan]

Bama,

You don't get the same results EVERY time you open the database?

Wayne

 

[Mike375]

Rnd() fills the column with different numbrs each time the query is opened.

Also, if do two calculated fields based on the same field as in

Exp1:Rnd([FieldName])
Exp2:Rnd([FieldName])

each will have different numbers

 

[WayneRyan]

Mike & Bama,

I really hate this topic ...

1) Close your database (exit Access)
2) Run your query and note what is returned
3) Close your database (exit Access)
4) Run your query and note what is returned

If you're OK, then I'm OK.

Wayne

 

[Mike375]

I know what you are saying but when I do it I have it open with a sort done and it will refresh on the sort.

I use it in telemarketing DBs. The A to Z on the Rnd field jumbles the records and also refreshes the query.

But I also use a "permanent" random by appending to a table with a Rnd default and then an update query is run for the main table for the field where the sort is done. That is done to give a random sort that holds for the day or whatever is required.

 

[WayneRyan]

Thanks Mike,

I'm OK with it for now.

Wayne