Loop Command Append Query - Run multiple times based on info in another table MS Access 2010

Can you be a little more specific?  Maybe provide an example of what you are trying to do with some made up data?

It should resemble something like this:

dim rsCustomers as recordset
dim rsTop10 as recordset
dim qdAppend as querydef
dim lngLoop as Long
set rsCustomers = dbEngine(0)(0).OpenRecordset("Select CustomerName From Customers")
set qdAppend = dbEngine(0)(0).QueryDefs("AddCustomerData")
Do Until rsCustomers.EOF
  set rsTop10 = dbEngine(0)(0).OpenRecordset("Select Top 10 * From TT ... Where TT.CustomerName = '" & rsCustomers!CustomerName & "'")
  Do Until rsTop10.EOF
    qdAppend!CustomerName = rsCustomers!CustomerName
    qdAppend!OtherFieldName = rsTop10!somefield
    qdAppend!OtherFieldName = rsTop10!someotherfield
    qdAppend!OtherFieldName = rsTop10!yetsomeotherfield
'  ...   repeat as needed
    qdAppend.Execute
    rsTop10.MoveNext
  Loop
  rsCustomers.MoveNext
Loop

Select allOpen in new window

This is basically it... You'll have to change your table/field/query names as appropriate:

Dim rs as Dao.recordset
Dim strSQL as string

strSQL = "SELECT DISTINCT Customer FROM Customer"

if rs.recordcount > 0 then
Set rs = currentDB.OpenRecordset(strSQL,dbopendynaset)

Do until rs.eof
      DoCmd.OpenQuery "YourAppendQuery"
      rs.MoveNext
Loop

End if  

rs.close
set rs = nothing

Select allOpen in new window

I put this in a module but I don't know how to get it to run.  DO I need to put it somewhere else?  Thank you!

Dim rs As Dao.Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCT [PolicyGroupRollUp] FROM [Policy Unique Rollup Qry]"

If rs.RecordCount > 0 Then
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rs.EOF
      DoCmd.OpenQuery "O-Step 3a Identified top 10 Dollars Current"
      rs.MoveNext
Loop

rs.Close

You should have sub/end sub lines in there to declare it with a name:

Sub RunAppendQueries
Dim rs As Dao.Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCT [PolicyGroupRollUp] FROM [Policy Unique Rollup Qry]"

If rs.RecordCount > 0 Then
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rs.EOF
      DoCmd.OpenQuery "O-Step 3a Identified top 10 Dollars Current"
      rs.MoveNext
Loop

rs.Close
End Sub

Select allOpen in new window

Once you do that, hit ctrl-G to take the cursor to the 'immediate window'.

Then just type in the name of the sub:

RunAppendQueries

... and hit enter.

reverse lines 7 and 8.

You cannot query the rs.recordcount without first opening the recordset

Perfect I did that and the last part I need is where I put the "End If" Statement.

<<reverse lines 7 and 8.>>

Whoops - I must have been half asleep when I wrote that.

I am getting the following error Object variable or With BLock variable not found, when I use the following

Sub RunAppendQueries()
Dim rs As Dao.Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCT [PolicyGroupRollUp] FROM [Policy Unique Rollup Qry]"

If rs.RecordCount > 0 Then
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rs.EOF


      DoCmd.OpenQuery "O-Step 3a Identified top 10 Dollars Current"

      rs.MoveNext
Loop
End If

rs.Close
End Sub

right after rs.close

Sub RunAppendQueries
Dim rs As Dao.Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCT [PolicyGroupRollUp] FROM [Policy Unique Rollup Qry]"


Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
Do Until rs.EOF
      DoCmd.OpenQuery "O-Step 3a Identified top 10 Dollars Current"
      rs.MoveNext
Loop

rs.Close

End If

End Sub

Select allOpen in new window

@mbizup

reverse lines 7 and 8.

Did you mean something like this?

nehT 0 > tnuoCdroceR.sr fI
)tesanyDnepObd ,LQSrts(tesdroceRnepO.bDtnerruC = sr teS

Select allOpen in new window

:-)

You have all been very helpful.  Just saved me from creating 124 appends.  Thank you so much!

Mark,

Not quite what I had in mind!

  ;-)

Have a great weekend!

ReportingSolutions -  Glad to help out. :-)

Mark,

It's been one of those mornings.  I stared at that about three seconds more than I should have, trying to figure out why I couldn't make heads or tails of it.  LOL!

I fed statements 7 and 8 into the StrReverse() function.

Now that's just beyond geeky.

I have an append query that pulls the top 10

Inquiring minds wonder about append

Do you really want to pull data out and knock it into a new table as a permanent real-time snapshot?  Or are you using that as a bridge to something else--like a report?  We may have saved you from creating 124 append queries -- but your code is still executing them.

Does it really need to do that?

ok almost there.  Thought I was done but...  The code runs and yields no results.  How does the policy table know what field to join to in the top 10 table?

Can you post what you have?  You have never actually given your table structure or provided any sample data.  Nick raises a good question, why?  What is the purpose of appending 10 records per customer into some table?

Oh, I need all the customers top 10 results to go into 1 table I then take that table and put it in an excel pivot so they can select whatever customer they want and get the top 10 results
I am new to asking questions.  Do I attach the dB?

You can attach a sample db if you want.  If you do, you should

1.  Sanitize your data.  No personal or proprietary information.  Make up names, company names, delete other proprietary information not needed for the process you want us to look at.

2.  Provide instructions for us so we know how to do what you want us to do.  
Example:  Please look at query1

3.  Ideally, given the data you provide us, it would be useful if you provide an example of what you want the results to look like.

Are you linking  your Excel application to the table you are trying to create?  It sounds like you could probably accomplish this without creating a new table by using a query.

However, given the original question appending data to a table I think you want something like:

Sub RunAppendQueries
Dim rs As Dao.Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCT [CustomerID] FROM [Customers]"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
Do Until rs.EOF
      strSQL = "INSERT INTO Table1 (CustomerID, field2, field3, field4) " _
                  & "SELECT Top 10 CustomerID, field2, field3, field4 " _
                  & "FROM SomeOtherTable " _
                  & "WHERE SomeOtherTable.CustomerID = " & rs!CustomerID _
                  & " ORDER BY SomeOtherTable.SortField"
      currentdb.Execute strsql, dbfailonerror
      rs.MoveNext
Loop

rs.Close

End If

End Sub

Select allOpen in new window

This code would take the top 10 records (and all matches based on the sorting field(s)) from table [SomeOtherTable] and insert them, one client at a time into Table1.

:)
So,  you want to create an Excel sheet!
Then we won't require append at all.

You'll need a query that selects all your customers
You'll want a parameterized select query that pulls out the Top 10 records you want

You'll walk once down the recordset of the query of customers
On each step, you'll pass in the customer to the parameterized query, and punch out the results to Excel

That, I can do!
Somebody else may be able to give you a hand automating the creation of the PivotReport -- that's not my speciality-- or you may do that bit by hand.

You won't want to post your production file and data.  Create a sample that has the moving parts involved, and just enough data -- and fake data -- that we can wrestle with it.

@fyed has the recordset walk down-pat.  It just needs some bits to track the current row, and the bits to knock the records into Excel instead of a table, and you're golden.

A stub sample that shows fieldnames from customers, and how customers relate to the table where the top 10 is coming from are needed.  And a sample Excel sheet that shows which fields you want in what columns is also needed.

It will take me a little time to get a dummy dB I will work on that but for the loop command I built that **See below**  It is giving me the following error.  I think if I can get that fixed I can use this loop


Run-time error '3075'  Syntax error (missing operator) in a query expression '[Policy Unique Rollup Qry].[PolicyGroupRollUp] =Customer1'.


Sub RunAppendQueries()
Dim rs As Dao.Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCT [PolicyGroupRollUp] FROM [Policy Unique Rollup Qry]"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
Do Until rs.EOF


      strSQL = "INSERT INTO [Opp Details Tbl] ( Period, Type, Policy_Group_Rollup, Policy_Group_All, OPP_NM, Savings, CountOfSRC_MBR_PGM_ID, [Year Month] )" _
                & "SELECT TOP 10  [O-Step 3 Identified top 10 Dollars Current].Period, [O-Step 3 Identified top 10 Dollars Current].Type, [O-Step 3 Identified top 10 Dollars Current].Policy_Group_Rollup, [O-Step 3 Identified top 10 Dollars Current].Policy_Group_All, [O-Step 3 Identified top 10 Dollars Current].OPP_NM, Sum([O-Step 3 Identified top 10 Dollars Current].ESYNC_VALUE) AS Savings, Count([O-Step 3 Identified top 10 Dollars Current].SRC_MBR_PGM_ID) AS CountOfSRC_MBR_PGM_ID, [O-Step 3 Identified top 10 Dollars Current].[Year Month]" _
                & "FROM [O-Step 3 Identified top 10 Dollars Current]" _
                & "WHERE [Policy Unique Rollup Qry].[PolicyGroupRollUp] = " & rs!PolicyGroupRollUp _
                & " Group BY  [O-Step 3 Identified top 10 Dollars Current].Period, [O-Step 3 Identified top 10 Dollars Current].Type, [O-Step 3 Identified top 10 Dollars Current].Policy_Group_Rollup, [O-Step 3 Identified top 10 Dollars Current].Policy_Group_All, [O-Step 3 Identified top 10 Dollars Current].OPP_NM, [O-Step 3 Identified top 10 Dollars Current].[Year Month]" _
                & "ORDER BY Sum([O-Step 3 Identified top 10 Dollars Current].ESYNC_VALUE) DESC"
 CurrentDb.Execute strSQL, dbFailOnError
      rs.MoveNext
Loop

rs.Close

End If

One takes it that this
strSQL = "SELECT DISTINCT [PolicyGroupRollUp] FROM [Policy Unique Rollup Qry]"
is the recordset that grabs the 'customers' as it is the outer part of the loop

This is your WHERE clause
"WHERE [Policy Unique Rollup Qry].[PolicyGroupRollUp] = " & rs!PolicyGroupRollUp _

Now, your syntax must be correct for this to work
if rs!PolicyGroupRollUp is a string ( "SomeFunnyAcronym") it will need to be surrounded by quotes.
I hate mega-quotations.  Chr(34) is the character quote so, if rs!PolicyGroupRollUp is string you'd need

"WHERE [Policy Unique Rollup Qry].[PolicyGroupRollUp] = " & chr(34) & rs!PolicyGroupRollUp & chr(34) _

I suspect that's where the bitchin about a 'missing operator' may be coming from.

And I don't think I'd do the SQL all in code.
Save this as a query (say as qapTopTen or some other name)

INSERT INTO [Opp Details Tbl] ( Period, Type, Policy_Group_Rollup, Policy_Group_All, OPP_NM, Savings, CountOfSRC_MBR_PGM_ID, [Year Month] )

SELECT TOP 10  [O-Step 3 Identified top 10 Dollars Current].Period, [O-Step 3 Identified top 10 Dollars Current].Type, [O-Step 3 Identified top 10 Dollars Current].Policy_Group_Rollup, [O-Step 3 Identified top 10 Dollars Current].Policy_Group_All, [O-Step 3 Identified top 10 Dollars Current].OPP_NM, Sum([O-Step 3 Identified top 10 Dollars Current].ESYNC_VALUE) AS Savings, Count([O-Step 3 Identified top 10 Dollars Current].SRC_MBR_PGM_ID) AS CountOfSRC_MBR_PGM_ID, [O-Step 3 Identified top 10 Dollars Current].[Year Month]

FROM [O-Step 3 Identified top 10 Dollars Current]
WHERE [Policy Unique Rollup Qry].[PolicyGroupRollUp] =[TheRollupName]

Group BY  [O-Step 3 Identified top 10 Dollars Current].Period, [O-Step 3 Identified top 10 Dollars Current].Type, [O-Step 3 Identified top 10 Dollars Current].Policy_Group_Rollup, [O-Step 3 Identified top 10 Dollars Current].Policy_Group_All, [O-Step 3 Identified top 10 Dollars Current].OPP_NM, [O-Step 3 Identified top 10 Dollars Current].[Year Month]

ORDER BY Sum([O-Step 3 Identified top 10 Dollars Current].ESYNC_VALUE) DESC

Select allOpen in new window

And test it to make sure it works right.
Your code can then be much simpler

Sub RunAppendQueries()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim qdf As QueryDef

strSQL = "SELECT DISTINCT [PolicyGroupRollUp] FROM [Policy Unique Rollup Qry]"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set qdf = CurrentDb.QueryDefs("qapTopTen")
If rs.RecordCount > 0 Then
    Do Until rs.EOF
        qdf.Parameters(0) = Chr(34) & rs!PolicyGroupRollUp & Chr(34)
        qdf.Execute , dbFailOnError
        rs.MoveNext
    Loop
End If
rs.Close
End Sub

Select allOpen in new window

Which you have to admit is much easier on the mind!

Thank you that worked but now I get too few parameters expecting 3.  Which to me makes no sense because this runs in a query :O(

Ok doing what you said now with the query not in vb thank you!

Nick67- You are awesome.  Thank you so much I took this changed the fields in my tables to the non _ fields and ran this and it works perfect. :O)

Now, do you want to knock it straight into Excel?  Or is this enough of a solution?

If you populate a recordset with results of the Select statement(s), you can use a .CopyFromRecordset range method to place the results directly into a worksheet.

Indeed--but we've got a loop of 124 selects.  Can you use .CopyFromRecordset to punch it further down the sheet.  I don't remember.  Since I usually need to format the sheet, I roll the records in via a loop

Why yes, yes you can :)
So here's the sample, with the query changed to a SELECT query, and code added to knock the results into oppDetails.xlsx on the desktop.

Now, after you get the results into Excel, turn on the macro recorder and create your pivot table.  Have a look at the code generated and clean it up and adapt it for Access.

Then you've got a one-click solution to create that export whenever you need one
ToPivot.mdb

Alter the code up a bit to knock in the column headers

Sub RunAppendQueries()
Dim x As Long
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim strSQL As String
Dim qdf As QueryDef
Dim fld As Field

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True
xlApp.UserControl = True

strSQL = "SELECT DISTINCT [PolicyGroupRollUp] FROM [Policy Unique Rollup Qry]"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set qdf = CurrentDb.QueryDefs("qapTopTen")
If rs.RecordCount > 0 Then
    qdf.Parameters(0) = rs!PolicyGroupRollup
    Set rs1 = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    For x = 0 To rs1.Fields.Count - 1
        xlSheet.cells(1, x + 1).Value = rs1.Fields(x).Name
    Next x
    rs1.Close
    Set rs1 = Nothing
    x = 2
    Do Until rs.EOF
        qdf.Parameters(0) = rs!PolicyGroupRollup
        Set rs1 = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
        xlSheet.Range("A" & x).CopyFromRecordset rs1
        x = x + rs1.RecordCount
        rs1.Close
        Set rs1 = Nothing
        'qdf.Execute dbFailOnError
        rs.MoveNext
    Loop
End If
rs.Close
Set rs = Nothing

xlBook.SaveAs Environ("USERPROFILE") & "\Desktop\oppDetails.xlsx"
xlApp.Quit

Select allOpen in new window

Great answer.  Easy to follow.  individuals responded so quickly and really helped me out.  I have been trying to do this on my own for days.  Thank you!!