Select Random Records Based on Conditions

[access123]

Hello,


I need to create a query that will give me the 5% of my total orders Randomly selected by order number by vendor, by city and by product category for Quality inspection

My Table looks Like this:

order_number vendorproduct_categorycityQC_Inspection1001PeterKitchenBoston1002CarlaFurnitureNY1003MikeElectronicArlington1004PeterKitchenBoston1005MikeElectronicNY

I run a query that gives me the Numbers of orders to be inspected by the vendor, Product category and city

vendorproduct_categorycityTotal Orders 5% for InspectionPeterKitchenBoston1005CarlaFurnitureNY201MikeElectronicArlington583MikeElectronicNY603

Now I need to run a nother query that will update the orders table and select the order based on the above query.

Could you please let me know how can I accomplish this.

Thanks!

[ajetrumpet]

this function will work to select just one:

Function RndRecord(fldInput As Variant, _
                   tblInput As String)

'******************************************************************************
'_____________________________________________________________________________*
'                                                                             |
'THIS FUNCTION ASSUMES THAT YOU HAVE A FIELD IN YOUR TABLE THAT IS A UNIQUE   |
'IDENTIFIER FOR EACH RECORD.  THE DATA TYPE CAN BE ANY TYPE.                  |
'_____________________________________________________________________________|
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 5/05/2006                                                              *
'Purpose: To return a random record in a dataset.                             *
'                                                                             *
'Arguments:                                                                   *
'fldInput > A field in the table that uniquely identifies each record.        *
'tblInput > The table name of which to return a random record from.           *
'                                                                             *
'******************************************************************************

On Error GoTo Cleanup

Dim db As DAO.Database
  Set db = CurrentDb
    Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT " & fldInput & " FROM " & tblInput, _
                                  dbOpenDynaset)

      Dim i As Integer
        Dim j As Integer
          Dim var As Variant
            Dim int1 As Integer
              Dim int2 As Integer
                Dim introw As Integer
                  Dim strVAR As String
                  
rs.MoveLast
rs.MoveFirst

var = rs.GetRows(rs.RecordCount)

Randomize
  
  For i = 0 To (rs.RecordCount - 1)

    int1 = Int(Rnd * (UBound(var, 2) - LBound(var, 2) + 1))
    int2 = Int(Rnd * (UBound(var, 2) - LBound(var, 2) + 1))

      While int1 = int2
        int2 = Int(Rnd * (UBound(var, 2) - LBound(var, 2) + 1))
      Wend

    For j = LBound(var, 1) To UBound(var, 1)
                                              
      strVAR = var(j, int1)
      var(j, int1) = var(j, int2)
      var(j, int2) = strVAR

    Next j

  Next i

RndRecord = var(0, 0)

Cleanup:
   rs.Close
   db.Close
      Set db = Nothing
        Set rs = Nothing
    
End Function '//LL

also, if you type in 'random record' into the vba help index, MS has their own example of how to do it. I'm sure you can adapt it to get what you need..