Connecting Tech Pros Worldwide Help | Site Map

Combine two tables randomly?

 
LinkBack Thread Tools Search this Thread
  #1  
Old January 7th, 2009, 01:56 PM
Member
 
Join Date: Jul 2007
Posts: 33
Default 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
Reply
  #2  
Old January 8th, 2009, 09:48 PM
tdw's Avatar
tdw tdw is offline
Familiar Sight
 
Join Date: Mar 2007
Location: Woodbridge, Virginia
Posts: 206
Default

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.
Reply
  #3  
Old January 8th, 2009, 10:51 PM
NeoPa's Avatar
Administrator
 
Join Date: Oct 2006
Location: London - UK
Age: 48
Posts: 14,330
Default

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
Reply
  #4  
Old January 9th, 2009, 03:09 PM
Member
 
Join Date: Jul 2007
Posts: 33
Default

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)
Reply
  #5  
Old January 9th, 2009, 06:08 PM
NeoPa's Avatar
Administrator
 
Join Date: Oct 2006
Location: London - UK
Age: 48
Posts: 14,330
Default

Good for you Art.

Remember. We're here if you get stuck :)
Reply
  #6  
Old January 9th, 2009, 11:50 PM
ADezii's Avatar
Expert
 
Join Date: Apr 2006
Location: Philadelphia
Age: 60
Posts: 4,812
Default

Quote:
Originally Posted by artemetis View Post
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)
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.  
Reply
  #7  
Old January 11th, 2009, 01:20 AM
NeoPa's Avatar
Administrator
 
Join Date: Oct 2006
Location: London - UK
Age: 48
Posts: 14,330
Default

Quote:
Originally Posted by ADezii View Post
before NeoPa states so! (LOL),
I wouldn't dream of it ADezii ;D
Reply
Reply

Bookmarks

Thread Tools Search this Thread
Search this Thread:

Advanced Search


Popular Articles

What is Bytes?

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 220,662 network members.