Combine two tables randomly?

[artemetis]

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

[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.

[NeoPa]

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

[artemetis]

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)

[NeoPa]

Good for you Art.

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

[ADezii]

@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)!

 
Dim intNumOfMales As Integer
Dim intNumOfFemales As Integer
Dim intNumOfRecs As Integer
Dim intCounter As Integer
Dim intRandomPosition As Integer
Dim strSQL As String
Dim MyDB As DAO.Database
Dim rstEmployees As DAO.Recordset
Randomize
'Need 7 or 8 Males and 7 or 8 Females
If Rnd() < 0.5 Then
  intNumOfMales = 7
Else
  intNumOfMales = 8
End If
intNumOfFemales = 15 - intNumOfMales
'Do the Males first, forget the Females for now
strSQL = "Select * From tblSuffolk Where [empGender] = 'M';"
Set MyDB = CurrentDb
Set rstEmployees = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
With rstEmployees
  .MoveLast
  .MoveFirst
    intNumOfRecs = .RecordCount       'Number of Males in tblSuffolk
  'Pick intNumOfMales Random Record Positions, move to that Record,
  'then Print the result
  For intCounter = 1 To intNumOfMales
    intRandomPosition = Int(Rnd * intNumOfRecs) + 1
      If intRandomPosition = 1 Then
        Debug.Print rstEmployees![empUID]
      Else
        .Move intRandomPosition - 1
        Debug.Print rstEmployees![empUID]
      End If
      rstEmployees.MoveFirst
  Next
End With
 
rstEmployees.Close
Set rstEmployees = Nothing
 

[NeoPa]

@ADezii
I wouldn't dream of it ADezii ;D