randolphoralph
Registered User.
- Local time
- Today, 06:39
- 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.
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