Splitting Data

[thesaguy]

Hi,

I use Access a lot to split large data files into groups, for versioning purposes.
Each group will receive its own creative version at the letter shop.

Currently, I use the following formula to do a 50/50 split on my data:


I populate a field with all “1”, then run the following update Query to change 50% of that field to a “2”:
“([ID]/2)-Round([ID]/2,0)” with a criteria of “0”

This works well for a 50/50 split of data with a sequential unique ID, problem is I don’t always have an ID and I don’t just need 50/50 splits.
Is there may be a Macro or some good code to do random splits of data on a percentage basis, and not just two, but possibly 3 way split? (I.e 35%/65% or 45%/33%/22% splits)
Thanks.

[TheShabz]

first I would query the count in that table. I would then calculate how many you want to update, call this X. then I would run a query to SELECT TOP X rnd(fieldname) FROM tablename... syntax is probably wrong there but you would be using the rnd() function to make it random.
then you join that query to the original table, and use an UPDATE query to set their modifier to whatever number. you then repeat the process but selecting another random set but this time setting criteria of your modifier not equal to whatever you set the first set at.
Lather. Rinse. Repeat.

[weekend00]

Use VBA code is the most efficient way.

Here assume there is a field "Mark" in the table to be marked.
call the sub twice if you want 2 way split:
up("table",30,"1") '30 perc "1"
up("table",71,"2") '70 perc "2"
to ensure all record are marked, use 71 instead of 70 in second call.

if want 3 way split:
up("table",33,"1")
up("table",44,"2")
up("table",100,"1")
VBA code:

Public Sub up(tableName As String, perc1 As Integer, markstr As String)
Dim recordCount As Long, records As Long
Dim i As Integer
Dim rs As DAO.Recordset
recordCount = CurrentDb.TableDefs(tableName).recordCount
records = Int((recordCount * perc1) / 100)
Set rs = CurrentDb.OpenRecordset("select mark from " + tableName + " where mark is null")
For i = 1 To records
rs.Edit
rs!mark = markstr
rs.Update
rs.MoveNext
If rs.EOF Then Exit For
Next
Set rs = Nothing
End Sub

[TheShabz]

That works to go from top down assigning numbers, but how would you address the randomization he wants?

[thesaguy]

Thanks Weekend00,

I've never used VB in Access,
Any way you could walk me through how to incorporate this, or possibly send ce a small Database with this done?
Email: vvwmisc@hotmail.com

Thanks.