Randomly select records to create limited record

In a database of Microsoft Access containing 500 employees of different categories I want only certain number of employees in each category. e.g., 2 employees of Category A, 5 employees of category B, 8 employees of category C.

Thus I want to create 10 separate groups wherein each person should be found in one list only, i.e. there should be no duplicate record. Each report should contain randomly selected unique record. One employee in one list should be found in another list.

Please help by clarifying how to achieve? I tried by creating union query. But, I got the result of duplicate records only. I also tried random limit expression but of no avail. Preferably the grouping should be based on the centre allotted.

The database looks as follows:-

Employee Name  Category  Duty As     Centre allotted 
1. XXXXX  开发者_如何学Go        A         I              1
2. XXXXX          A         I              1
3. XXXXX          B         II             1
4. XXXXX          B         II             1
5. XXXXX          B         II             1
6. XXXXX          C         III            1  
7. XXXXX          C         III            1  
8. XXXXX          C         III            1  
9. XXXXX          C         III            1  

How do I do this please?

Would something like this suit?

SELECT p.ID, p.EmpName, p.Category
FROM Persons P
WHERE p.ID In (
    SELECT Top 2 ID 
    FROM Persons 
    WHERE Category = "A" 
    ORDER BY Rnd(ID)+ID) 
OR p.ID In (
    SELECT Top 5 ID 
    FROM Persons 
    WHERE Category = "B" 
    ORDER BY Rnd(ID)+ID) 
OR p.ID In (
    SELECT Top 8 ID 
    FROM Persons 
    WHERE Category = "C" 
    ORDER BY Rnd(ID)+ID)
ORDER BY p.Category

One approach to do this would be to add a field to the employees table called flag with a yes/no data type. Then create a form with the following controls on it:

Command button with a caption of reset flag to no for all records

A combo box that lists all the different categories

A text box to store the number of records to select

Another command button with a caption of flag records

Then the on the click event VBA code would look something like the following:

    Private Sub cmdFlagRecords_Click()
    Dim lngRandomNumber As Long
    Dim k As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    strSQLText = "Select * From Employees Where Category='" & Me.cboCategory & "'"
    Set rs = db.OpenRecordset(strSQLText, dbOpenSnapshot)
    k = 0
    Do Until k = CLng(Me.txtRecordsToSelect)
        ' generate a random number between one and the number of rows available
         lngRandomNumber = CLng(Rnd(1) * rs.RecordCount)
         'move to that record
         rs.AbsolutePosition = lngRandomNumber
         If Not rs![flag] Then
             ' set the flag to yes or true and increment the k variable
             db.Execute "Update Employees Set=True Where ID=" & rs![ID]
             k = k + 1
         End If
    Loop
    rs.Close

End Sub

You can then click the button as many times as necessary to build up your selection.