randomly selecting records

How can I randomly select records?
haberlansAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrianWrenCommented:
Let's say that your table, ("TheTbl"), has a unique field, ("ID"), which is numeric, (like an autonumber).

Write yourself a function, say,

Public Function RndSelect() as Long

   Dim lo as long
   Dim hi as Long

   lo = DMin("ID", "TheTbl")
   hi = DMax("ID", "TheTbl")

   RndSelect = (hi - lo) * rnd(lo) + Lo

End Function

Then in a query put

SELECT TOP 1 *
FROM TheTbl
WHERE TheTbl.ID => RndSelect();

The RndSelect Function will get the highest and lowest ID numbers from the table, find the span between them, select a random portion of that span and add it to the bottommost number, and return that value.

The >= in the query will handle any cases where a record has been deleted, leaving a gap.

Brian
0
tomk120999Commented:
Hi, haberlans, welcome aboard!  Can you help with a little more information?  I'm not sure I understand "randomly".  Can you tell me what you mean by random, what you are selecting records or info from, a table, a query, a form or report?  Are you choosing what is random or is a user generating some sort of request or choosing from a list?  If you can elaborate some, I'm sure we can find a solution.

good luck as usual...
0
tomk120999Commented:
Hi, haberlans, welcome aboard!  Can you help with a little more information?  I'm not sure I understand "randomly".  Can you tell me what you mean by random, what you are selecting records or info from, a table, a query, a form or report?  Are you choosing what is random or is a user generating some sort of request or choosing from a list?  If you can elaborate some, I'm sure we can find a solution.

good luck as usual...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

tomk120999Commented:
Brian, you and I must have crossed light sabres, that's why the double post. :)
0
BrianWrenCommented:
Just to be on the safe side, you should probably put

SELECT TOP 1 *
FROM TheTbl
WHERE TheTbl.ID => RndSelect()
ORDER BY TheTbl.ID;

Brian
0
haberlansAuthor Commented:
I am doing a database for a Senator that would like to send out surveys to voters. From this database I need to randomly select people so that a good random sample of people is selected. This will then be used for a mailing labels. Thanks for the help!
0
AlexVirochovskyCommented:
Other way: get randomally record
Function GetRandRecord(NameTable as
string,PrimaryKey as String) as Vaiant
Dim R as Recordset
Dim DB as database
Dim NumberRecords as long
Dim Result as Long

Se db = currentdb()
Set R = db.OpenRecordSet(NameTable,dbOpenDynaset)
r.MoveLast
NumberRecords = r.RecordCount
Randomyze Timer
Result = Int( (NumberRecords*Rnd)+1)-1
R.AbsolutePosition = Result
GetRandomallyRecord = r(PrimaryKey)
End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlexVirochovskyCommented:
Ouups:
Randomize Timer 'of course
0
BrianWrenCommented:
You're welcome for the help.  Interesting application...

BTW, the 'Comments' offered here are offered for points.  Don't forget to grade the comment of the person who helped your specific problem the most.  Just select the 'Accept Comment As Answer' link for the comment that helped, and then grade the response.

Happy New Year!
Happy New Decade!
Happy New Century!
Happy, . . . (Well, you get the point).

Brian
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.