Random Query

• • Reply

  13 years ago

  by vera

  neha bhatia

  , India

  Joined 13 years ago

  Hi!

  I need to run a query to select records from the database in Access randomly. Please let me know of the SQL query for this particular requirement..

  Thanks a lot!

  Report abuse
• • Reply

  13 years ago

  by JC IRVIN

  JC IRVIN CALDZ

  bacolod, Philippines

  Joined 13 years ago
  hi,
  
  im new here... i know a way on how to randomly pick out a data but not using sql...
  im just using the DAO..
  
  let me know if this problem havent been solved yet...
  
  the trick is with the
  ================================
  data1.recordset.abolutepostion = intstrng
  ================================
  where in we have to find a way to supply a value for the intstrng.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  13 years ago

  by jefftullin

  Jeff Tullin

  , United Kingdom

  Joined 13 years ago

  Here is an off-the wall appraoch:

  Assuming you have a column in the table which is a numeric ID called ID

  Add a new column called SortOrder, as long integer

  Run an update query against the table just before you want to read the random rows.

  The update would be

  Update  thetable  set  SortOrder  =(Rnd()*999999) +[ID]

   

  By including ID in the query, the random function is called on every line. So all rows gets a random number (maybe some duplicates, but that wont be a problem)

   

  Then, you just   Select   * from  thetable order by SortOrder;

  If you want random numbers of rows, noble the update query so that it generates numbers in a small range.

  Update  thetable  set  SortOrder  = ((Rnd()*999999) +[ID]) mod 10;

   

  Then select where SortOrder = 6 or something. You will get a different set of values and a different number of rows each time.

   

   

   

   

  Report abuse