LIMIT parameter

[ConfusedNAccess]

I've created a query, and in a module formed a code to randomly select records for an audit sample from a query. I'm stumped on the next part... I'm trying to accomplish the following:
The results contain various fields of data for 6 different individuals. Criteria for the query is if the parts order date and service date are all in the current month.

Individuals = REP_ID
Customer Sales = Sales_ID
Customer Name = CustName
Parts order date = Part_ID
Service Date = Service_ID

I understand that LIMIT clause doesn't work in MS Access, that I am to use the top n. Something just isn't clicking here...

I want this to be a parameter query.. so the user can define the criteria to limit the results. i want to see the top X of all records from the random sample.. based on the Rep_ID.

Basically I need all of this to be ramdom sampling but limited to 6 individuals and the number of records returned for each individual should be prompted by the user. maybe this should be done in code.. not sure.. i'm still an untrained novice.. learning on my own as i go..

am i making this out to be more difficult then it should be? I've included the code that runs the "ramdonSample"

Sub PickRandom()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim strSQL As String
Dim strTableName As String

' 1: Create a new temporary table containing the required fields
strSQL = "SELECT LastCalcs.sales_id, Lastcalcs.CustName " & _
"INTO tblTemp " & _
"FROM lastcalcs;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 2: Add a new field to the new table
Set db = CurrentDb()
Set tdf = db.TableDefs("tblTemp")
Set fld = tdf.CreateField("RandomNumber", dbSingle)
tdf.Fields.Append fld

' 3: Place a random number in the new field for each record
Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
rst.MoveFirst
Do
Randomize
rst.Edit
rst![RandomNumber] = Rnd()
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing

' 4: Sort the data by the random number and move the top 25 into a new table
strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT TOP 25 tblTemp.sales_ID, tblTemp.borrowername " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 5: Delete the temporary table
db.TableDefs.Delete ("tblTemp")
End Sub

 

[AlexCuse]

This maybe?

strSQL = "SELECT TOP " & _
InputBox "Please Enter A Number" & _
" tblTemp.sales_ID, tblTemp.borrowername " & _
             "INTO " & strTableName & " " & _
             "FROM tblTemp " & _
             "ORDER BY tblTemp.RandomNumber;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ConfusedNAccess]

Love the quote by the way!!

Silly question maybe.. but where do i put this? in the same module? or... other option?

 

[ConfusedNAccess]

ok, got the input box to work, however when prompted, and user inputs number, it limits the results to that figure. I need it to limit the results to each individual.

example-

Joey has 20 records
Tom has 10 records
Lisa has 5 records

when user inputs number, lets say... 3
then I want to see 3 records for each
Joey's 3 records
Tom's 3 records
Lisa's 3 records..

any thoughts?