Changing the "order" of records in a table 1

[dfwelch]

I am going to use the following code to work my way through 32,000 records in a table and use a random number generator to select records randomly from varying-sized subsets of the 32,000 records. I know that the function will run much faster and be easier to write if the records are sorted the way I want them sorted (which would place together those subsets I mentioned), as opposed to the way they were originally imported from a text file (which would mean I would have to go through all 32,000 records for each of 160 subsets of records). I have tried sorting them and saving the changes, but when I run the code, it's still the ORIGINAL 20th record that gets modified.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
strSQL = "SELECT UniverseTemp.Sampled FROM UniverseTemp;"
Set rs = db.OpenRecordset(strSQL)
For x = 1 To 20
    If x = 20 Then
      rs.edit
      rs!Sampled = True
      rs.Update
    End If
    rs.MoveNext
Next x

I know I could probably create 160 smaller tables for the subsets of records, but I would rather avoid that.

 

[oharab]

I'm not sure what it is you are doing here!
If all you are doing is ordering the records by a certain field, ID number for instance, you can specify that in the SQL:
strSQL="SELECT UniverseTemp.Sampled FROM UniverseTemp ORDER BY UniverseTemp.ID ASC;"

Can you explain some more what it is you are trying to do.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[dfwelch]

That worked! Stay tuned though, I still have a lot of work to do getting the whole random record thing to work!

 

[Escale]

I first use the randomize function in Access to start the random number generator. Put this statement somewhere when you open your database:

Randomize

You will need only to execute it once to start the random number generator.

Then whenever you need a random number use the Rnd function. Rnd return a real, equal to 0 but less than 1. So to get a random number between 1 and 25000 you could use this snippet of code:

Dim upperbound, lowerbound, RandomInt As Long
upperbound = 25000
lowerbound = 1

RandomInt = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

MsgBox "Random number this time is: " & RandomInt

I have used this many times with no problems. To fine more resources look at the help file under “rnd function”.

Life is meant to be hard, so work hard, play hard,
--- Dan (Escale)

 

[dfwelch]

Thank you much Dan.
I haven't had as much time today to work on it as I'd like, but I do know that I will have a question about the Rnd function returning repeats.

For example, in one instance, I may need to select 10 random records out of 100. So, I need to make sure that I get 10 DIFFERENT random numbers. Should I write code that simply checks each number against random numbers previously generated, and simply generate another if any are repeats? Or, can I be sure that I will not get repeats unless I run the Randomize function again?

 

[Golom]

Building on the code from Escale, rather than flipping through the records with

For x = 1 to RandomInt
   if x = RandomInt then
      ' Update Code
   endif
   rs.MoveNext
Next n

You can just

rs.MoveFirst
rs.Move RandomInt
' Update Code

 

[dfwelch]

Thanks Golom, that will be a HUGE help!!
Do you have any advice regarding my concern about possible repeating records? If my table has 100 records and I need 10 selected randomly, can I be sure that in 10 consecutively generated random numbers that none are repeats? I mean, I could store the 10 numbers in an array and just scroll through the array with each new number generated to make sure it's not a repeat.

 

[Golom]

Given that you are marking each record with rs!Sampled = True, you could just handle dups with

Randomize
ProcessedRecords = 0
RandomRecords = 20
UpperBound = 25000
LowerBound = 1

Do Until ProcessedRecords >= RandomRecords 

   RandomInt = Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)
   If RandomInt > 0 Then

' Need to check because Move 0 is the current record.

      rs.MoveFirst
      rs.Move RandomInt

      If rs!Sampled = False then
         rs.Edit
         rs!Sampled = True
         rs.Update
         ProcessedRecords = ProcessedRecords + 1
      End If
   End If

Loop

Your chances of hitting duplicates on selecting 20 records out of 25,000 are fairly small so there's not much penalty for doing a move to a record that you're not going to process.

You can of course do the array thing but, if you are going that route I recommend a collection. A collection is self organizing and you can find an entry in a collection (or not find it) with one statement as opposed to having to loop through an array.

 

[oharab]

Another way to defur this feline:

http://www.mvps.org/access/queries/qry0011.htm

gives a method of selecting a random set of records.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------