[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 818
  • Last Modified:

randomly selecting records

How can I randomly select records?
0
haberlans
Asked:
haberlans
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now