1. select distinctrow old_table.name into new_tableA 2. create new_TableB 3. change your random record selector to loop 25500 times, randomly selecting a record from new_tableA, inserting it into new_tableB and deleting it from new_tableA.
τΏτ Tony -----Original Message----- From: Sam Roach [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 1:22 PM To: CF-Talk Subject: OT: MS Access random records I need to grab a random 25000 records from a database, no dups.. I found some code online and modified it, but it is to slow... Works great for just a few records.. The query.. SELECT DISTINCTROW old_table.Name INTO new_table FROM old_table WHERE (((InStr(1,rndSet(25500,'old_table'),("," & [id] & ",")))<>0)); Here is The function.. Function rndSet(maxNumber, tablename) As String Dim dbs As Database, rst As Recordset Dim strSQL As String Dim str Dim cnt As Long Dim cnt1 As Long Dim rndMax As Long Dim RndNumber As Long Dim str2 Dim cnt3 Set dbs = CurrentDb strSQL = "SELECT * FROM " & tablename Set rst = dbs.OpenRecordset(strSQL) rst.MoveLast cnt = CLng(rst.RecordCount) cnt3 = CLng(maxNumber) If cnt3 > cnt Then cnt1 = CLng(rst.RecordCount) Else cnt1 = cnt3 End If rndMax = cnt str = "," str2 = "'" Do Until cnt1 = 0 Randomize RndNumber = Int(Rnd * rndMax) If (InStr(str2, CStr(RndNumber)) = 0) And RndNumber <> 0 Then str2 = str2 & RndNumber & "','" cnt1 = cnt1 - 1 rst.MoveFirst rst.Move RndNumber If cnt1 = 0 Then str = str & rst("id") & "," Exit Do Else str = str & rst("id") & "," End If End If Loop rndSet = str rst.Close Set dbs = Nothing End Function ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4