Macros

[LadyDiss]

At the risk of showing my stupidity in Access, is it possible to record a macro by recording keystrokes or do you have to know code in Access? I have a query that has a column whereby everyone needs assigned to a group randomly. I have the column as a lookup but rather than going through manually and clicking the down arrow for each group and assigning, just wanted to streamline breaking up 600 people into 6 groups!

 

[Alexandre]

Answer to your first question is no, I am afraid (I mean, it may be possible but... you would have to write code for that). Now, I have the impression that if you want to avoid code, you might be able to do what you want through queries. Could you be more specific on how you need to associate people to groups (according to what rule?) and on the underlaying tables and fields?

Alex

[This message has been edited by Alexandre (edited 09-06-2001).]

 

[LadyDiss]

Thank you for your reply. I was afraid the answer was no. Here is what I have . . . 300 people are attending a conference and need to be broken into 8 equal groups for break out session. We want them mixed up randomly so not all one department or office is in the same group (so they get to know other people). I currently have the table set up with the attendees names, dept, location, and a field for group which currently is a lookup field to a table that contains the groups and it is a drop down to pick from (group names such as Red, Green, Blue, Purple, etc.). They have not been broken into the groups yet and other than choosing from the drop down and then cutting and pasting, I was looking for a quicker way.

 

[The_Doc_Man]

If you are willing to take a chance that the groups won't be QUITE uniform in size, you might put a [Group Number] field (integer) in your table for each person. Then work on the idea of writing an Update query where you update the group number based on a formula: Expr1: CInt( 8.0 * Rnd()) + 1

This will randomly populate your groups. You could then write a report that did a group break on [Group Number], put in a footer, get group size counts, and if you didn't like the distribution, just run the query again and again until you get something you like?

 

[Alexandre]

The following will give you a pseudo-random distribution of your attendees into 8 groups equal as possible (given that 300 = 8*37+4).
As the DocMan said, your lookup table needs to have a numeric Primary Key ranging from 1 to 8: GroupID, which will be the One side of the Attendees-Groups relationship.

We will create and populate a GroupID in your Attendees table, that will be the foreign key (Many side of the relationship).

I also assume that your Attendees table as a numeric PK ranging from 1 to 300 (AttendeesID). If this is not the case, re-post for a workaround.

1.
SELECT Attendees.*, ([AttendeesID] Mod 3)+1 AS GroupID
FROM Attendees;

This select query makes the distribution, creating and populating the GroupID field. Save it.

2.
MAKE A COPY of your attendees table (copy and paste it with a different name in the tables window)

3.
Transform the above query into a Make-Table one: open it in edit mode, then in the queries Menu choose Make-Table. Give the name of your Attendees table, you will be prompted for a confirmation.

4.
BE SURE that you made that copy.

5.
Confirm


The result is not actually a random distribution since it is made according to a regular pattern, and your attendees in your Attendees table may also have be entered according to some logic. So this could not be used as panel for statistic purposes for example.

Nevertheless, it should be a satisfying approach to a random distribution for your specific purpose.

Alex