Re: [MS_AccessPros] randomly chosen groups

 

Russ-

Ignore my previous message.  Now that I've actually read through Graham's code, it's a pretty elegant solution.

He sorts the records from your table on a random number, then reads through them one at a time, assigning a group number in sequence to each record.  So, the first record gets assigned 1, the second 2, the third 3, and so on up to the number of groups specified, then it cycles back to 1 again.  If the number of records isn't an exact multiple of the number of groups, one or more groups will have one fewer records than the rest.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 4, 2017, at 1:38 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Graham,

The groups do not have to be perfectly divided.  A couple of them larger or smaller than the others won't make a big difference in this case because there are approximately 130 to divide up.  For myself, I am not very skilled at writing code, so I can try the solutions and hopefully won't come up with errors I cannot deal with.

Russ

On 3/3/2017 4:48 PM, graham@mandeno.com [MS_Access_Professionals] wrote:

Hi Russ,

Wow! I hope tomorrow turns out a whole lot better for you!

I think there is a problem with dividing the number of records by 4 and using a SELECT TOP N query, because, unless the number of records is a multiple of 4 you will end up with the last group being unbalanced.

For example, is you have 25 records, your quotient will be 7 (otherwise the 25th record will be unassigned).  This will give you groups of 7, 7, 7 and 4, when presumably it would be better to have 7, 6, 6 and 6.

Also, I would prefer a solution that avoids a temporary table if possible.

Given this, here is a solution using VBA and DAO:

Const cNumGroups = 4

Dim db as DAO.Database

Dim rs as DAO.Recordset

Dim iCount as Long

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT [Group Number] FROM [Your Table] " _

        & "ORDER BY Rnd([House Number]);", dbOpenDynaset)

With rs

    Do Until .EOF

        ![Group Number] = (iCount Mod cNumGroups) + 1

        .MoveNext

        iCount = iCount + 1

    Loop

    .Close

End With

Set rs = Nothing

Set db = Nothing

Please note that this is "air code" (neither tested nor compiled) so it may contain typos and other errors :)

Best regards,

Graham

---In MS_Access_Professionals@yahoogroups.com, <dyspoz2@...> wrote :

Errr, make that two flats.  Ugh. 

Sent from my iPhone 6s

On Mar 3, 2017, at 11:07 AM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Man, you're NOT having a good day!  Hope it gets better for you.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 3, 2017, at 5:56 PM, Russ dyspoz2@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I'll try and get to all of these responses later on today.  Doctor appointment and flat tire on my vehicle.  

Russ

On 3/3/2017 8:11 AM, John Viescas JohnV@... [MS_Access_Professionals] wrote:

Russ-

Ah, it just occurred to me that you need to be sure to put "1" in all the inserted records.  So, you need to do:

INSERT INTO Group1 ([House Number], [Group Number], <all the rest of the field names>)

(SELECT TOP nn [House Number], 1, <all the rest of the field names>

FROM MyTable

ORDER BY [Group Number], [House Number]);

You can actually do subsequent inserts into the same new table (not four separate tables) to avoid having to do a UNION of them all in the end.  Just make sure you use the correct literal for Group Number on each new insert.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 3, 2017, at 2:53 PM, John Viescas <johnv@...> wrote:

Russ-

Yes, that would be a surer way to ensure you get four equal groups.  On the first pass, I would assign numbers 1-4 and start with the "1" records and then pick "2" records until you get to ¼ of the total.  That way, you get enough records even if enough weren't assigned "1".

Do the UPDATE as Graham suggested and then do:

INSERT INTO Group1

(SELECT TOP nn FROM MyTable

ORDER BY [Group Number], [House Number]);

.. where nn is one-fourth of the total.

Then to do the DELETE:

DELETE * FROM MyTable

WHERE [House Number] IN

(SELECT [House Number] FROM [Group1]);

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 3, 2017, at 1:57 PM, Russ dyspoz2@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John and Graham,

Since this method will probably generate odd totals of households in the four groups would it be possible to do this a different way?  I slept on it and thought of this:  

Maybe put all the records in a temporary table, then pick a random 1/4 of the records and remove them to a tempgroup1 table, then take a random one third of the remaining households and move them to a tempgroup2 table, then take half of those remaining and remove them to a tempgroup3 table.  Then assign the group numbers as required and move them all back to the original table.  Is that possible?

Russ

On 3/2/2017 9:54 PM, graham@... [MS_Access_Professionals] wrote:

Hi Russ

 

You don't actually need the Mod operation, because the Rnd() function returns a number between 0 and 0.99999999…  Thus, multiplying the result by 4 will give a number between 0 and 3.99999999…

 

You need to use the Int function to ensure this result is always rounded DOWN, and then finally add 1 if you want (1,2,3,4) instead of (0,1,2,3).

Thus, your UPDATE query  will look something like this:

UPDATE [Your Table Name] SET [Group Number]=Int(Rnd([House Number]) * 4) + 1;

Best regards,

Graham

---In MS_Access_Professionals@yahoogroups.com, <dyspoz2@...> wrote :

John,

Should I put this in the query field where I want the random number applied?  Such as in the Group Number field?  And I do want the groups to be numbered 1-4 so would the statement be modified like this?

Group:  (Rnd([Household Number]) * 4) Mod 5

Russ

You can use the Rnd function to do what you want in a query.

Group:  (Rnd([Household Number]) * 4) Mod 4

That will give you a random number between 0 and 3 to assign your groups.  Add 1 if you want groups 1 through 4.  Use that in an UPDATE query to assign the values.

NOTE:  You WILL get random numbers, but there's no guarantee that an equal number of households will be assigned to each group.

John Viescas, Author

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4756/14048 - Release Date: 03/02/17

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4756/14050 - Release Date: 03/03/17

 

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7998 / Virus Database: 4756/14050 - Release Date: 03/03/17

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (17)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___