Problem with randomly selecting records from table (1 Viewer)

randolphoralph

Registered User.
Local time
Today, 13:24
Joined
Aug 4, 2008
Messages
101
I have a Module titled Random that is suppose to randomly select 3000 records from a table. The problem I am running into is that as the Names table has higher number of records in it...there are more records selected. For example if there are 100,000 records in the Names table the code will create a new table with 3003 random records in it instead of 3000 providers.

I have included a zip file with a copy of the database in order to show the problem at hand....If you download this please go into the Module titled Random and run it to see the New table created with more than 3000 records as specified in the Module.

I have spent about a week trying to solve this issue and can not seem to figure it out. Also, if you do not already have it selected go to Tools > References and check the entry for Microsoft DAO 3.6 Object Library and click OK. If you don't find 3.6 then 3.5 will do.

If anyone could solve this problem it would make my day.



Code:
Option Compare Database
Option Explicit
Function PickRandomProviders()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strTableName As String
    
    ' 1: Create a new temporary table containing the required fields
    strSQL = "SELECT Names.FirstName, Names.LastName " & _
             "INTO tblTemp " & _
             "FROM Names;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    ' 2: Add a new field to the new table
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblTemp")
    Set fld = tdf.CreateField("RandomNumber", dbDouble)
    
    tdf.Fields.Append fld
    
    ' 3: Place a random number in the new field for each record
    Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
    rst.MoveFirst
    Do
        Randomize
    rst.Edit
            rst![RandomNumber] = Rnd()
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    
    rst.Close
    Set rst = Nothing
    
    ' 4: Sort the data by the random number and move the top 3000 into a new table
    strTableName = "Random_Name_List"
    strSQL = "SELECT TOP 3000 TblTemp.FirstName, TblTemp.LastName " & _
             "INTO " & strTableName & " " & _
             "FROM tblTemp " & _
             "ORDER BY tblTemp.RandomNumber;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    ' 5: Delete the temporary table
    db.TableDefs.Delete ("tblTemp")
End Function
 

Attachments

  • Names.zip
    319.7 KB · Views: 100

vbaInet

AWF VIP
Local time
Today, 19:24
Joined
Jan 22, 2010
Messages
26,374
I have a Module titled Random that is suppose to randomly select 3000 records from a table. The problem I am running into is that as the Names table has higher number of records in it...there are more records selected. For example if there are 100,000 records in the Names table the code will create a new table with 3003 random records in it instead of 3000 providers.

I have included a zip file with a copy of the database in order to show the problem at hand....If you download this please go into the Module titled Random and run it to see the New table created with more than 3000 records as specified in the Module.

I have spent about a week trying to solve this issue and can not seem to figure it out. Also, if you do not already have it selected go to Tools > References and check the entry for Microsoft DAO 3.6 Object Library and click OK. If you don't find 3.6 then 3.5 will do.

If anyone could solve this problem it would make my day.



Code:
Option Compare Database
Option Explicit
Function PickRandomProviders()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strTableName As String
    
    ' 1: Create a new temporary table containing the required fields
    strSQL = "SELECT Names.FirstName, Names.LastName " & _
             "INTO tblTemp " & _
             "FROM Names;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    ' 2: Add a new field to the new table
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblTemp")
    Set fld = tdf.CreateField("RandomNumber", dbDouble)
    
    tdf.Fields.Append fld
    
    ' 3: Place a random number in the new field for each record
    Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
    rst.MoveFirst
    Do
        Randomize
    rst.Edit
            rst![RandomNumber] = Rnd()
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    
    rst.Close
    Set rst = Nothing
    
    ' 4: Sort the data by the random number and move the top 3000 into a new table
    strTableName = "Random_Name_List"
    strSQL = "SELECT TOP 3000 TblTemp.FirstName, TblTemp.LastName " & _
             "INTO " & strTableName & " " & _
             "FROM tblTemp " & _
             "ORDER BY tblTemp.RandomNumber;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    ' 5: Delete the temporary table
    db.TableDefs.Delete ("tblTemp")
End Function


My first guess would be the number of records your recordset is producing. Just before the line where you close rst, debug.print rst.RecordCount to see how many it returns. Or use a message box
 

randolphoralph

Registered User.
Local time
Today, 13:24
Joined
Aug 4, 2008
Messages
101
My first guess would be the number of records your recordset is producing. Just before the line where you close rst, debug.print rst.RecordCount to see how many it returns. Or use a message box

Well I am very confused....I added the debug.print rst.RecordCount right before the line where I closed rst and now it is returning 3000 records as it should. Just to test I commented out the debug.print rst.RecordCount and ran the code again and it is still doing 3000 records. Do I need to leave debug.print rst.RecordCount or can I delete it now that the problem seems to be fixed? Also, what does debug.print rst.RecordCount do?
 

Rabbie

Super Moderator
Local time
Today, 19:24
Joined
Jul 10, 2007
Messages
5,906
I noticed you are sorting your table on just one field. If there are duplicates then TOP will return extra records. When you get more records than you expect, check if this what's causing the probem
 

vbaInet

AWF VIP
Local time
Today, 19:24
Joined
Jan 22, 2010
Messages
26,374
Well I am very confused....I added the debug.print rst.RecordCount right before the line where I closed rst and now it is returning 3000 records as it should. Just to test I commented out the debug.print rst.RecordCount and ran the code again and it is still doing 3000 records. Do I need to leave debug.print rst.RecordCount or can I delete it now that the problem seems to be fixed? Also, what does debug.print rst.RecordCount do?


Something's happening with your recordset. To fill up your recordset you must move to the last record. Then just move back to the first record before performing any operations on it. Put these lines just before rst.MoveFirst

If rst.BOF = True Then
' Maybe you could do something here if you wish, but understand what this line does
Set rst = Nothing
Exit Function
End If
rst.MoveLast
------------
rst.MoveFirst

Check your help files for BOF. It will return true if there are no records in the recordset and you will get an error if you try to movelast if there are no records (well I get errors in such cases) so always best to be safe.

Yes you can delete Debug code

(oops, i wrote just after the great Rabbie. my apologies :))
 

Khalid_Afridi

Registered User.
Local time
Today, 22:24
Joined
Jan 25, 2009
Messages
491
I noticed you are sorting your table on just one field. If there are duplicates then TOP will return extra records. When you get more records than you expect, check if this what's causing the probem

I totally agree with Mr. Radbbie

Its happen with me also, I got 25 records instead of TOP 20. I noticed that there are records duplication which produce the error.

so I would suggest to check for duplication in your recordset.
 

Users who are viewing this thread

Top Bottom