Do you have any enemies? Geez.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, March 03, 2017 11:28 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] randomly chosen groups
Errr, make that two flats. Ugh.
Sent from my iPhone 6s
On Mar 3, 2017, at 11:07 AM, John Viescas JohnV@msn.com [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
(Paris, France)
On Mar 3, 2017, at 5:56 PM, Russ dyspoz2@cox.net [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.
On 3/3/2017 8:11 AM, John Viescas JohnV@msn.com [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
(Paris, France)
On Mar 3, 2017, at 2:53 PM, John Viescas <johnv@msn.com> 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
(Paris, France)
On Mar 3, 2017, at 1:57 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John and Graham,
On 3/2/2017 9:54 PM, graham@mandeno.com [MS_Access_Professionals] wrote:
Hi Russ
---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
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (13) |
Tidak ada komentar:
Posting Komentar