Randomized Allocation w/in Category - Loop

S

stillsarah

Registered User.
Local time
Today, 11:45
Joined
Jan 26, 2019
Messages
14
Hi All,

I have a database with 200 individual IDs that are grouped by town into 10 groups of 20 people. I want to "randomly" assign 60% of each town (12 people per town) a value of 1 and the rest 0. Is there a way to do this through a query? VBA is fine too.

Thanks,
Sarah
 
I've been piecemealing, assuming I could figure out how to combine the two sections.

Basically, there are two levels of randomization. Your comments on the other thread helped me with one level, and now I'm trying to work on the other piece.

Might be easier to explain the whole of what I'm trying to do:

I want to administer a survey to people in two types of towns: low-saturation and high-saturation. In a high saturation town, 100% of the people will be offered the survey. In a low-saturation town, 60% will be offered it. This allocation should be "random". Of the people who take the survey, 30% should be assigned treatment = 0, 40% = 1, 20% = 2, 10% =3. (This distribution has changed since the last thread and I've adapted the table accordingly).

I appreciate all that you did on the other post, but ultimately I still don't exactly understand how you did it, which i guess is why I didn't realize this was a different iteration of the same code.

I understand how I could get the 60/40 split, but how could I run this through each group of observations, rather than the entire database? For example, if I have 100 people, with 20 each in Albany, Auburn, Atlanta, Boise, and Baton Rouge, how could I get that 60% within each city, rather than 60% as a whole.

Thanks,
Sarah
 
Code: 
I understand how I could get the 60/40 split, but how could I run this through each group of observations, rather than the entire database? For example, if I have 100 people, with 20 each in Albany, Auburn, Atlanta, Boise, and Baton Rouge, how could I get that 60% within each city, rather than 60% as a whole

That is just pure probability. If you do them in groups of 20 or one big group it does not matter. Every time you draw a record it has 60% chance to be a 1 and 40% chance to be 0. The size of the group does not matter Now this is a random draw and it has variance. So you will have some variance and might not be quite 60, 40 in the short run. In the long run it will be closer and closer. The variance is more noticeable in the smaller sample size.
You flip a coing 10 times you may get 7 heads and three tails. That is not that unlikely. But it is hightly unlikely you would see 700 heads and 300 tails if you flip a thousand times.

So you understand you will get roughly 60/40 but you could get noticeable variance in a group of 20. If you want to get exactly 60/40 then you would have to modify the code. You would randomly assign until either .6 or the group has been assigned a 1 or .4 of the group has been assigned a 0 at that point at that point all the remaining get the 1 or zero to ensure the split.
 
To demonstrate I reran the function after changing the distribution in the table. This table has 50 states and 20 records per state. This assigns a 1 or 0 from a 60/40 distribution.

Code: 
Town	Random_Assignment	CountOfRandom_Assignment
Albany	1	14
Albany	0	6
Annapolis	0	8
Annapolis	1	12
Atlanta	0	9
Atlanta	1	11
Augusta	0	7
Augusta	1	13
Austin	0	11
Austin	1	9
Baton Rouge	1	11
Baton Rouge	0	9
Bismarck	0	5
Bismarck	1	15
Boise	0	7
Boise	1	13
Boston	0	6
Boston	1	14
Carson City	0	5
Carson City	1	15
Charleston	0	8
Charleston	1	12
Cheyenne	0	7
Cheyenne	1	13
Columbia	0	14
Columbia	1	6
Columbus	0	6
Columbus	1	14
Concord	0	6
Concord	1	14
Denver	0	6
Denver	1	14
Des Moines	0	7
Des Moines	1	13
Dover	0	7
Dover	1	13
Frankfort	0	13
Frankfort	1	7
Harrisburg	1	13
Harrisburg	0	7
Hartford	0	7
Hartford	1	13
Helena	0	8
Helena	1	12
Honolulu	0	9
Honolulu	1	11
Indianapolis	0	6
Indianapolis	1	14
Jackson	0	8
Jackson	1	12
Jefferson City	1	12
Jefferson City	0	8
Juneau	0	8
Juneau	1	12
Lansing	0	7
Lansing	1	13
Lincoln	0	10
Lincoln	1	10
Little Rock	0	9
Little Rock	1	11
Madison	1	16
Madison	0	4
Memphis	0	6
Memphis	1	14
Minneapolis	0	6
Minneapolis	1	14
Montgomery	0	11
Montgomery	1	9
Montpellier	0	8
Montpellier	1	12
Oklahoma City	0	7
Oklahoma City	1	13
Olympia	0	9
Olympia	1	11
Phoenix	0	10
Phoenix	1	10
Pierre	0	8
Pierre	1	12
Providence	0	11
Providence	1	9
Raleigh	0	7
Raleigh	1	13
Richmond	0	9
Richmond	1	11
Sacramento	0	7
Sacramento	1	13
Salem	0	12
Salem	1	8
Salt Lake City	1	13
Salt Lake City	0	7
Santa Fe	0	8
Santa Fe	1	12
Springfield	0	7
Springfield	1	13
Tallahassee	0	11
Tallahassee	1	9
Topeka	0	7
Topeka	1	13
Trenton	1	9
Trenton	0	11

What you will see is that there is variance (that is probability). So you would expect most states to have 12(1) and 8(0) but you will get variability. Using basic statistics you can get a very good estimate of this. Sometimes you get 11/9, or 13/7 but there are some outliers as well.

Code: 
One	Zero	Count	Split	Distr
16	4	1	80 / 20	2%
15	5	2	75 / 25	4%
14	6	8	70 / 30	16%
13	7	14	65 / 35	28%
12	8	9	60 / 40	18%
11	9	6	55 / 45	12%
10	10	2	50 / 50	4%
9	11	5	45 / 55	10%
8	12	1	40 / 60	2%
7	13	1	35 / 65	2%
6	14	1	30 / 70	2%
You can see above that 18% of the time each state gets a 60/40 split. And 28% of the states got a 65/35 split. But you can see at the very top and bottom the extreme outliers. This is not a mistake this is probability. Of interest in the long run there is exactly 400 0s and 600 1s over the set. And that is not unlikely, but just by chance that it is exact.

So that is how a random draw would work. If you did each group individually, you would get nearly an identical distribution. If a true random draw is not what you need, but a random exact 60 / 40 split you will need to say so. The latter is doable but requires a little tweak to the code. This may be the same for your other distribution as well if you need the exact distribution versus a pure random draw.
 
Last edited:
Thanks for the explanation. For the first level (this 60/40) it needs to be exact at the town level. I understand it won't be truly "random." The dist from the other thread should be random and is great as is.

Thanks,
Sarah
 
I did this and tried to make it as generic as possible so that it could be used with many distributions and different tables and with other problems. This is a somewhat common request. Often when people say they want to assign randomly they do not actually mean this. For example if you randomly assigned people to available tasks and you had ten people and 30 tasks, you really mean each person is assigned 3 tasks. In pure random someone may get 0 and another person 6. Would not be real popular.

It is kind of complicated. I do not have time explain now, but I will explain this evening. Then hopefully you can enter what I did into your db. You can look at the table and see that the distribution is forced. Every city gets 8 and 12.

Forcing the exact distribution is much more complicated because you have to know about the table you are putting the values in, what the group is (the city), find how many records are in a group (city), find how many values are allowed (12 ones, and 8 0s), how many of each value is currently in a group, determining if the group is filled.
 

Attachments

I knew in this case it wouldn't be random in the mathematical sense. It's why I put "random" in quotes several times.

If you have time later to write up how to force the 60/40, that would be much appreciated. Thanks again for your help.
 
I will get back this PM, but I may be over thinking. I was focused on making a completely automated solution. To do this more manually with a few queries would be very simple.
You would simply build a couple update queries. You would group the top 40 percent of each group sorted randomly. THen use that in an update query to assign 0. Modify it to get the remaining unassigned and update to 1. Not fancy but very easy to do. A top by group is a little tricky, but pretty common.
 
Awesome - the more simple the better. Doesn't need to be completely automated.
 
I don't know if this fits your situation, but it may be useful. It's a few years old, but it did solve the poster's question.
 
Hi Sarah,

I think what you want would be easily done in VBA using a recordset in which you group by city and run a top 12 insert into a temp table.

Have a look at the attached and see if that helps you.

Cheers,
Vlad
 

Attachments

This is one of those things that is just easier to hardwire. Run this code to get your 60 40. I was originally focused on the original issue of how to do a more complex distribtuion, and my solution would do that but it is a lot to digest. If interested I can go through it, but if you are really looking for an answer this does the job.

Code: 
'------------------------------------------  Simple Code
'
' Here is some simple code to solve the specific problems

Public Sub Assign60_40()
  Dim rsTowns As DAO.Recordset
  Dim rs As DAO.Recordset
  Dim strSQL As String
  Dim Town As String
  Const TableName = "Randomization_Practice"  'Update as needed
  Const FieldToUpdate = "Random_Assignment"
  'set all values to zero
  CurrentDb.Execute "Update " & TableName & " SET " & FieldToUpdate & " = 0"
  strSQL = "SELECT DISTINCT Town from " & TableName
  Set rsTowns = CurrentDb.OpenRecordset(strSQL)
  'loop each town
  Do While Not rsTowns.EOF
    Town = rsTowns!Town
    'grab the top 60 percent randomly
    strSQL = "Select Top 60 Percent * from " & TableName & " WHERE Town = '" & Town & "' order by myRandom([ID])"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    Do While Not rs.EOF
      'set to one
      rs.Edit
        rs.Fields(FieldToUpdate) = 1
      rs.Update
      rs.MoveNext
    Loop
    rsTowns.MoveNext
  Loop
End Sub
 
From database3.accdb in post #12:
Code: 
Option Compare Database

Option Explicit

Public Sub vcRandomize60_40()
Dim rCity As DAO.Recordset, sCity As String, sSQL As String

'empty temp table
CurrentDb.Execute "DELETE * FROM tmpRndPerson;", dbFailOnError

'loop through cities and add first 12 random persons to temp table

Set rCity = CurrentDb.OpenRecordset("SELECT tblPerson.City FROM tblPerson GROUP BY tblPerson.City ORDER BY tblPerson.City;", dbOpenSnapshot)
rCity.MoveFirst
Do Until rCity.EOF
    sCity = rCity("City")
    
    sSQL = "INSERT INTO tmpRndPerson ( City, RNDID, PersonID, rndVal )" & _
    " SELECT TOP 12 tblPerson.City, Rnd([PersonID]) AS RNDID, tblPerson.PersonID, 1 AS RndVal " & _
    " FROM tblPerson GROUP BY tblPerson.City, tblPerson.PersonID, 1 HAVING tblPerson.City = '" & sCity & "' " & _
    " ORDER BY tblPerson.City, Rnd([PersonID]);"

    CurrentDb.Execute sSQL, dbFailOnError
    'next city
    rCity.MoveNext
Loop

MsgBox "Done"
End Sub
 
You must log in or register to reply here.

Similar threads

H
Using a query that creates a random result as the criteria for the a field in another table
Replies
3
Views
730
ebs17
E
A
Solved Exporting Table to Excel by Category
Replies
9
Views
826
abrody
A
P
Need VBA Code Guidance - BeforeUpdate Event
Replies
6
Views
651
The_Doc_Man
The_Doc_Man
Noson5434
Conditional Formatting
Replies
12
Views
1,199
theDBguy
theDBguy
R
Unique values
Replies
15
Views
761
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom