Random records

[travismp]

I need to generate a random list of records. I have a table with a couple thousand records. I want to build a query that when I click on it, it will ask

"What percentage of records do you want to see" if I type "50" the query will randomly return 50% of my records. Randomly being the key word.

So how do I make a query to RANDOMLY give a result set based on a number my user types in? thanks.

 

[MSAccessRookie]

The Mailman has solved this in the past so I will credit him.

Step 1
Add a field with the RND function in it, sort on this field. Save this query as QueryRND

Step 2
Create a new query,
Select top 10 * from queryRND

Done

The Random Number function is RND ( { Optional Starting Seed } )

The Starting Seed is a Number, and the return value is a number between 0 and 1 ( like 0.212232410907745 )

I guess you would need to add a column to your query for the Random number, ORDER BY that column, and select top n% as indicated by the user request.

 

[MSAccessRookie]

Update:

Here is an example query to get the top 25% of records from a table called table4

select TOP 50 Percent * from
(
SELECT PrimaryKey, rnd(PrimaryKey), INT( PrimaryKey*rnd(PrimaryKey) )+1 from table4
)
Order by 2 ;

NOTE: You would replace PrimaryKey with a non-repeating integer column of your own

 

[Jon K]

I would recommend using WayneRyan's GetRand() function posted in this thread (Post #9):-
http://www.access-programmers.co.uk/forums/showthread.php?t=141780

Public Function GetRand(SomeField As Variant) As Double
   Randomize
   GetRand = Rnd()
End Function

I have seen that many examples using Rnd() have one common flaw. They start with the same sequence of random numbers every time Access starts up so each time we start Access and run a query basing off of Rnd(), the SAME set of random records is returned by the query.

Wayne's GetRand() function, when used in a query, successfully returns DIFFERENT set of random records every time Access starts up.
.

 

[MSAccessRookie]

Good Point Jon K, Adding this function should give him what he wants

 

[EMP]

So how do I make a query to RANDOMLY give a result set based on a number my user types in?

To address the second part of your question -

You'll have to build the query SQL statement on the fly. The number in the Top predicate doesn't accept a parameter value.

^