Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-30-2019, 04:10 PM   #1
stillsarah
Newly Registered User
 
Join Date: Jan 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
stillsarah is on a distinguished road
Randomized Allocation w/in Category - Loop

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

stillsarah is offline   Reply With Quote
Old 01-30-2019, 04:31 PM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,821
Thanks: 35
Thanked 553 Times in 524 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Randomized Allocation w/in Category - Loop

Sarah,
I am totally confused. Why are you posting this, and why in a new thread? We went through how to do this in great detail.
https://www.access-programmers.co.uk...d.php?t=303470
This is the exact same problem only with a different distribution.

bottomRange TopRange ReturnValue
0 .4 0
.4 1 1
MajP is offline   Reply With Quote
Old 01-30-2019, 04:45 PM   #3
stillsarah
Newly Registered User
 
Join Date: Jan 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
stillsarah is on a distinguished road
Re: Randomized Allocation w/in Category - Loop

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

stillsarah is offline   Reply With Quote
Old 01-30-2019, 05:13 PM   #4
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,821
Thanks: 35
Thanked 553 Times in 524 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Randomized Allocation w/in Category - Loop

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.
MajP is offline   Reply With Quote
Old 01-30-2019, 06:34 PM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,821
Thanks: 35
Thanked 553 Times in 524 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Randomized Allocation w/in Category - Loop

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 by MajP; 01-30-2019 at 06:46 PM.
MajP is offline   Reply With Quote
Old 01-31-2019, 08:35 AM   #6
stillsarah
Newly Registered User
 
Join Date: Jan 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
stillsarah is on a distinguished road
Re: Randomized Allocation w/in Category - Loop

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
stillsarah is offline   Reply With Quote
Old 01-31-2019, 09:09 AM   #7
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,821
Thanks: 35
Thanked 553 Times in 524 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Randomized Allocation w/in Category - Loop

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.
Attached Files
File Type: accdb RandomDis V2.accdb (616.0 KB, 10 views)

MajP is offline   Reply With Quote
Old 01-31-2019, 09:17 AM   #8
stillsarah
Newly Registered User
 
Join Date: Jan 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
stillsarah is on a distinguished road
Re: Randomized Allocation w/in Category - Loop

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.
stillsarah is offline   Reply With Quote
Old 01-31-2019, 10:06 AM   #9
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,821
Thanks: 35
Thanked 553 Times in 524 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Randomized Allocation w/in Category - Loop

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.
MajP is offline   Reply With Quote
Old 01-31-2019, 10:09 AM   #10
stillsarah
Newly Registered User
 
Join Date: Jan 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
stillsarah is on a distinguished road
Re: Randomized Allocation w/in Category - Loop

Awesome - the more simple the better. Doesn't need to be completely automated.
stillsarah is offline   Reply With Quote
Old 01-31-2019, 10:32 AM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,214
Thanks: 90
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Randomized Allocation w/in Category - Loop

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
stillsarah (01-31-2019)
Old 01-31-2019, 11:35 AM   #12
bastanu
Registered User
 
Join Date: Apr 2010
Location: Kelowna, Canada
Posts: 242
Thanks: 1
Thanked 69 Times in 67 Posts
bastanu is on a distinguished road
Re: Randomized Allocation w/in Category - Loop

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
Attached Files
File Type: accdb Database3.accdb (432.0 KB, 10 views)
bastanu is offline   Reply With Quote
Old 01-31-2019, 04:56 PM   #13
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,821
Thanks: 35
Thanked 553 Times in 524 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Randomized Allocation w/in Category - Loop

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
MajP is offline   Reply With Quote
Old 01-31-2019, 05:11 PM   #14
bastanu
Registered User
 
Join Date: Apr 2010
Location: Kelowna, Canada
Posts: 242
Thanks: 1
Thanked 69 Times in 67 Posts
bastanu is on a distinguished road
Re: Randomized Allocation w/in Category - Loop

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

bastanu is offline   Reply With Quote
Reply

Tags
loop , randomization , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
allocation problem steve111 General 0 06-08-2014 09:31 PM
Stock Allocation Phil280372 Introduce Yourself 0 11-20-2012 12:33 PM
Random allocation jamariani General 2 08-20-2012 11:47 AM
Randomized phone number from each unique postal code based on 4 Cities ChrisRocks Queries 1 12-09-2009 02:25 PM
Allocation database mambo21 General 0 03-30-2005 11:26 AM




All times are GMT -8. The time now is 09:15 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World