Problem executing SQL statement in module

[Guest]

I have the following module that I want to execute a sql statement but I
can't get the syntax to work.

Public Sub Test()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("_QryMatchedUWList")

Do Until rst.EOF

DoCmd.RunSQL

"INSERT INTO TblPickedSample ( Underwriter_ID,
Mortgage_Loan_No___10_Character)" & _
"SELECT TOP 4 tblMatchedVolume.Underwriter_ID,
tblMatchedVolume.Mortgage_Loan_No___10_Character," & _
"FROM tblMatchedVolume" & _
"WHERE (((tblMatchedVolume.Underwriter_ID)=" & "'" & rst!UnderwriterID & "'"
& "));"

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

I am using Access 2002 and this is for a local DB not SQL Server.

The end goal is to select four records from TblMatchedVolume for each
Underwriter_ID. THe SQL statement runs as a query but I can only make it
work if the Underwriter_ID is entered via a prompt. I have nearly 300
Underwriter_ID's and I want to avoid running the query 300 times.

Any ideas on how to fix the module or a better way to go about this?

Thanks.

 

[Josh Nankivel via AccessMonster.com]

I'm thinking you can probably do an aggregate query that groups by
underwriter id and then do your top 4 on the other field. You may need to
join to your other query if you want to limit the underwriter id list based
on the results of _QrymatchUWList.

 

[Guest]

The table will have ) records that match for some underwriter_ID's and some
may have over 100. I have run an aggregate so I get teh underwriter_ID and
then a count of the loans they have done. Whenever I take the top 4 it
limits my results to 4 total. I need 4*273 = 1092 results. I'm not sure why
gettng the aggregate will make any difference. Can you explain more?

Thanks for the response.

 

[Tim Ferguson]

"INSERT INTO TblPickedSample " & _
" (Underwriter_ID, Mortgage_Loan_No___10_Character)" & _
"SELECT TOP 4 " & _
" Underwriter_ID, " & _
" Mortgage_Loan_No___10_Character," & _
"FROM tblMatchedVolume" & _
"WHERE Underwriter_ID=" & "'" & rst!UnderwriterID & "';"
[snip]

The end goal is to select four records from TblMatchedVolume for each
Underwriter_ID. THe SQL statement runs as a query but I can only make
it work if the Underwriter_ID is entered via a prompt. I have nearly
300 Underwriter_ID's and I want to avoid running the query 300 times.

You don't say what you mean by "can't get the SQL syntax to work" -- the
command itself looks legal to me, although TOP 4 is meaningless without
an ORDER BY clause.

Have you checked the spelling of all the field names? Having multiple
underscore characters is good way to create such bugs.

The VB code as you posted it is uncompilable, so I assume it has been
cut-and-pasted in bits. You don't have a rst.MoveNext statement, so you
are going to get a very large if boring PickedSample table. Are you
running out of memory to store it in?

Best of luck


Tim F