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

I have an append query that pulls the top 10 I need it to run multiple times.  1 time for each customer I have.  I have the customers in another table.  does anyone have the vb to call the field from the one table and loop it thru the append table?

Thank you!!
ReportingSolutionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Can you be a little more specific?  Maybe provide an example of what you are trying to do with some made up data?
0
aikimarkCommented:
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

Open in new window

0
mbizupCommented:
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

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ReportingSolutionsAuthor Commented:
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
0
mbizupCommented:
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

Open 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.
0
Dale FyeCommented:
reverse lines 7 and 8.

You cannot query the rs.recordcount without first opening the recordset
0
ReportingSolutionsAuthor Commented:
Perfect I did that and the last part I need is where I put the "End If" Statement.
0
mbizupCommented:
<<reverse lines 7 and 8.>>

Whoops - I must have been half asleep when I wrote that.
0
ReportingSolutionsAuthor Commented:
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
0
Dale FyeCommented:
right after rs.close
0
mbizupCommented:
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

Open in new window

0
aikimarkCommented:
@mbizup
reverse lines 7 and 8.
Did you mean something like this?
nehT 0 > tnuoCdroceR.sr fI
)tesanyDnepObd ,LQSrts(tesdroceRnepO.bDtnerruC = sr teS

Open in new window


:-)
0
ReportingSolutionsAuthor Commented:
You have all been very helpful.  Just saved me from creating 124 appends.  Thank you so much!
0
Dale FyeCommented:
Mark,

Not quite what I had in mind!

  ;-)

Have a great weekend!
0
mbizupCommented:
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!
0
aikimarkCommented:
I fed statements 7 and 8 into the StrReverse() function.
0
mbizupCommented:
Now that's just beyond geeky.
0
Nick67Commented:
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?
0
ReportingSolutionsAuthor Commented:
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?
0
Dale FyeCommented:
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?
0
ReportingSolutionsAuthor Commented:
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?
0
Dale FyeCommented:
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

Open 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.
0
Nick67Commented:
:)
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.
0
ReportingSolutionsAuthor Commented:
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
0
Nick67Commented:
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.
0
Nick67Commented:
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

Open 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

Open in new window


Which you have to admit is much easier on the mind!
0
ReportingSolutionsAuthor Commented:
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(
0
ReportingSolutionsAuthor Commented:
Ok doing what you said now with the query not in vb thank you!
0
Nick67Commented:
Ok,

Here's a sample based -- EXTREMELY -- roughly on what you've posted.
It does 124 appends of top ten to OppDetails
I am not a fan of [] so my sample has gotten rid of spaces and _ in the field names

The nuts-and-bolts are there.
Have a look.

Next is to kick it out to Excel straight-off, instead of appending to a table
The Top Tens button is the trigger
test.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ReportingSolutionsAuthor Commented:
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)
0
Nick67Commented:
Now, do you want to knock it straight into Excel?  Or is this enough of a solution?
0
aikimarkCommented:
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.
0
Nick67Commented:
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
0
Nick67Commented:
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
0
Nick67Commented:
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

Open in new window

0
ReportingSolutionsAuthor Commented:
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!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.