Combine two tables randomly?

Ok, let's try this....

I've got two tables: tblSuffolk, tblNassau.

Both tables have the same EXACT fields:

empUID - self explanatory
empFirstName - self explanatory
empLastName - self explanatory
empGender - self explanatory
empCounty - county where employee works
empSelected - value entered after employee has been selected for a group
empGroupNumber - container for automatically assigned group number once selected for a group
x3
x4

I need to write a query that randomly creates a group of up to 30 employees (half male/ half female), with 15 being from tblSuffolk and 15 being from tblNassau.

In my mind's eye, I visualize a query running this. Once I'm satisified with the results of the query, I can enter a value in empSelected, so the employee cannot be selected for the next run and I could have a unique group number entered in the empGroupNumber field.

Ideally, I'd like to do this until all 900 employees have been split into groups.

Am I reaching for the stars here?

Thanks!
Art

Wow. I am curious as to the answer to this as well. Can Access do random number generation?

You could always create code that assigns a number to each button on the keyboard, runs some sort of calculation on a text field entry, and have a cat walk across your keyboard.

I can see no way this could work in SQL, but VBA could do it at a stretch I think (using recordset processing).

Add an extra field to the tables and process through all the records assigning successive integer values to each record of a certain gender. That would mean maintaining two counts simultaneously for each table. The field should be indexed (may have to allow Nulls for the initial state) along with the gender field (a compound key comprising both fields).

When that's ready, your code should generate random numbers between the range you know to be available for each set of data, then grab the records in turn (flag them as selected). Finding a record that hasn't already been selected can be achieved using a "trial and error" approach if none better presents itself.

I hope this helps.

PS I love that you call your field empGender. I truly have nothing against sex, but I hate to see the term misused so often ;D

Thanks folks!!! The more I looked at what I was trying to do (and as I lay in bed staring at the ceiling)...I realized I'd have to go the "RST" route.....

I love this website...always great people to help out!

NeoPa - I have the same peeve..... :o)

Good for you Art.

Remember. We're here if you get stuck :)

The following Algorithm will Print the Random UIDs of either 7 or 8 Males (15 total Males and Females) from tblSuffolk. To generate Random Females, and to parallel the functionality in the other Table, is easy from this point on. I basically threw this together and I must admit it is a tad too much, before NeoPa states so! (LOL), but it is nonetheless a viable solution. There is definately room for improvement, and it does not check against the possibility of generating the same Record Position more than once. It did work on test data, to my surprise (LOL)!

I wouldn't dream of it ADezii ;D