By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,453 Members | 932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,453 IT Pros & Developers. It's quick & easy.

How can I choose random data

P: 1
i have large amount of data- i want to choose from it random
one record only every time
Jul 5 '10 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 15k+
P: 31,768
We'd like to help if we can, but there is too little here to enable us to understand even what you're asking. Please explain the question in more than a single sentence, giving info we'd need to understand the question.
Jul 5 '10 #2

Expert 5K+
P: 8,701
@Marwa Ahmed
Here is a little System which I created, that will return the Primary Key Value of a Random Record within a Data Set. Once you have this PK Value, you will now have the capability to Filter for this Single Record in any manner you so desire. For this Demo, I used the Employees Table of the Northwind Sample Database with a couple of extra Records added. Any questions, please feel free to ask. I commented the code as much as possible without trying to be annoying (LOL):
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstRandom As DAO.Recordset
  3. Dim lngNumOfRecs As Long
  4. Dim lngRndRec As Long
  5. Dim lngRndPKValue As Long
  7. Set MyDB = CurrentDb
  8. Set rstRandom = MyDB.OpenRecordset("Employees", dbOpenSnapshot)
  10. Randomize       'Seed the Random Number Generator
  12. With rstRandom
  13.   .MoveLast: .MoveFirst     'Needed for accurate Record Count
  14.   lngNumOfRecs = .RecordCount
  15.     lngRndRec = Int(Rnd() * lngNumOfRecs) + 1       'Obtain a Randmom Record Position between
  16.                                                     '1 and lngNumOfRecs
  17.       'Move to that Random Record Position allowing for the Offset. AbsolutePosition
  18.       'Property of a Recordset may be used under certain conditions, but is not 100% reliable
  19.       .Move (lngRndRec - 1)
  20.         'Retrieve the Value of the Primary Key [EmployeeID] at this Record
  21.         lngRndPKValue = ![EmployeeID]
  22.           Debug.Print "Random Record Number is: " & Format$(lngRndRec, "0000") & " with a Primary Key Value " & _
  23.                       "of " & Format$(![EmployeeID], "0000")
  24. End With
  26. rstRandom.Close
  27. Set rstRandom = Nothing
Sample OUTPUT for 16 Trails:
Expand|Select|Wrap|Line Numbers
  1. Random Record Number is: 0003 with a Primary Key Value of 0003
  2. Random Record Number is: 0014 with a Primary Key Value of 0015
  3. Random Record Number is: 0002 with a Primary Key Value of 0002
  4. Random Record Number is: 0012 with a Primary Key Value of 0012
  5. Random Record Number is: 0004 with a Primary Key Value of 0004
  6. Random Record Number is: 0014 with a Primary Key Value of 0015
  7. Random Record Number is: 0013 with a Primary Key Value of 0014
  8. Random Record Number is: 0006 with a Primary Key Value of 0006
  9. Random Record Number is: 0003 with a Primary Key Value of 0003
  10. Random Record Number is: 0002 with a Primary Key Value of 0002
  11. Random Record Number is: 0010 with a Primary Key Value of 0010
  12. Random Record Number is: 0013 with a Primary Key Value of 0014
  13. Random Record Number is: 0016 with a Primary Key Value of 0017
Jul 5 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.