By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,596 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Combine two tables randomly?

P: 76
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
Jan 7 '09 #1
Share this Question
Share on Google+
6 Replies


tdw
100+
P: 206
tdw
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.
Jan 8 '09 #2

NeoPa
Expert Mod 15k+
P: 31,186
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
Jan 8 '09 #3

P: 76
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)
Jan 9 '09 #4

NeoPa
Expert Mod 15k+
P: 31,186
Good for you Art.

Remember. We're here if you get stuck :)
Jan 9 '09 #5

ADezii
Expert 5K+
P: 8,599
@artemetis
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)!
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim intNumOfMales As Integer
  3. Dim intNumOfFemales As Integer
  4. Dim intNumOfRecs As Integer
  5. Dim intCounter As Integer
  6. Dim intRandomPosition As Integer
  7. Dim strSQL As String
  8. Dim MyDB As DAO.Database
  9. Dim rstEmployees As DAO.Recordset
  10. Randomize
  11. 'Need 7 or 8 Males and 7 or 8 Females
  12. If Rnd() < 0.5 Then
  13.   intNumOfMales = 7
  14. Else
  15.   intNumOfMales = 8
  16. End If
  17. intNumOfFemales = 15 - intNumOfMales
  18. 'Do the Males first, forget the Females for now
  19. strSQL = "Select * From tblSuffolk Where [empGender] = 'M';"
  20. Set MyDB = CurrentDb
  21. Set rstEmployees = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  22. With rstEmployees
  23.   .MoveLast
  24.   .MoveFirst
  25.     intNumOfRecs = .RecordCount       'Number of Males in tblSuffolk
  26.   'Pick intNumOfMales Random Record Positions, move to that Record,
  27.   'then Print the result
  28.   For intCounter = 1 To intNumOfMales
  29.     intRandomPosition = Int(Rnd * intNumOfRecs) + 1
  30.       If intRandomPosition = 1 Then
  31.         Debug.Print rstEmployees![empUID]
  32.       Else
  33.         .Move intRandomPosition - 1
  34.         Debug.Print rstEmployees![empUID]
  35.       End If
  36.       rstEmployees.MoveFirst
  37.   Next
  38. End With
  39.  
  40. rstEmployees.Close
  41. Set rstEmployees = Nothing
  42.  
Jan 10 '09 #6

NeoPa
Expert Mod 15k+
P: 31,186
@ADezii
I wouldn't dream of it ADezii ;D
Jan 11 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.