Query Help: Selecting records for each instance of value in a table?

[mpscan]

I have the following query:

strOperatorID = "+M"

strSQL = "SELECT TOP 15 tblTemp.CLAIM_NUMBER, tblTemp.OPERATOR_ID " & _
"INTO tblTemp2 " & _
"FROM tblTemp " & _
"WHERE OPERATOR_ID = '" & strOperatorID & "' " & _
"ORDER BY tblTemp.RandomNumber;"


However, I do not want to manually enter strOperatorID, which
corresponds to OPERATOR_ID in tblTemp. I would like this query to run
through and put the TOP 15 into tblTemp2 for every instance of
OPERATOR_ID in the source table.

In this case, there are four unique OPERATOR_ID's in the source, so I
would have 60 records (15 for each) in tblTemp2.

Any ideas?

 

[John Spencer]

You can loop through the source table and run the query once for each record
in the source table. Or you can modify the query.

First solution: (UNTESTED AIRCODE)
Assumption:
You are using DAO (MDB)
Your source table is named tblSource and has a field named OperatorID

Dim DbAny as DAO.Database
Dim rstSource as DAO.Recordset
Set DbAny = CurrentDB()
Set rstSource = DbAny.OpenRecordSet ("SELECT OperatorID FROM tblSource")

If rstSource.RecordCount > 0 then
With rstSource
While Not .EOF
strOperatorID = !OperatorID
'your SQL code here

.MoveNext
Wend
End With
End If

Second Solution: (UNTESTED SQL)
SELECT TblTemp.ClaimNumber, tblTemp.Operator_ID
FROM TblTemp
INTO TblTemp2
WHERE TblTemp.RandomNumber in (
SELECT TOP 15 T1.RandomNumber
FROM TblTemp as T1 INNER JOIN tblSource
ON T1.Operator_ID = tblSource.OperatorID
WHERE tblSource.OperatorID = tblTemp.OperatorID
ORDER BY tblTemp.RandomNumber)

 

[mpscan]

Thanks for your help, I really appreciate it!

I can't seem to get the SQL to work (never was good with loops..lol) I
get a syntax error in the FROM using this:

strSQL = "SELECT tblTemp.CLAIM_NUMBER, tblTemp.PAYEE_ID,
tblTemp.PAID_DATE, tblTemp.OPERATOR_ID, tblTemp.MEM_PRODUCT,
tblTemp.MSG_CODE " & _
"FROM tblTemp " & _
"INTO tblTemp2 " & _
"WHERE tblTemp.RandomNumber in (SELECT TOP 15
T1.RandomNumber FROM tblTemp as T1 INNER JOIN tblClaimResolvers ON
T1.OPERATOR_ID = tblClaimResolversp.OPERATOR_ID " & _
"WHERE tblClaimResolvers.OPERATOR_ID =
tblTemp.OPERATOR_ID);"

..... any other idea?

 

[John Spencer]

Well, I'm not sure but I see at least one error in the On clause of the
subquery.
tblClaimResolversp should probably be tblClaimResolvers

I usually use Debug.Print StrSQL and then copy the SQL into a new query and
try to execute it there. That usually gives me better error messages then
when I run it from code. Once I see the error there, then I can change the
VBA code to correctly generate the SQL statement.