Loop through table elements as filter in a query

[pliskers]

I need some VBA to loop through a query (output to be exported) using each element of a table as a filter criteria in the query.

Assume the table is named Markets and I have the Market field in Query1. How can I run the query for each individual Market in my table one at a time?

Thanks!

 

[Micron]

Create a recordset on the field [Market] from table [Markets], move to the first record in the rs and loop through that set. On each loop you get the value of [Market] and pass it to the sql statement as criteria and execute it. Move to the next record and repeat the loop. That's the best I can say at this time with the info you posted. Have to say I don't know what this means either - using each element of a table

 

[pliskers]

By "element" I meant each record in the Markets table, which is just a one-field list.

Would you possibly be able to offer a sample of code? As I mention, I want to use each market in that table as a filter in a query that includes the Market as a criteria against a separate table of data.

 

[Micron]

You haven't said what type of query it is and I need the sql statement that you want to run.
If the table and field names are not exactly as posted, I need those too.
I presume that the values in Market are unique (no dupes).
I also presume that the query is going to be output somewhere after it filters on each value of Market and you will fill in that part.

 

[pliskers]

It's a Select Query that will be exported as an Excel file for each value in the SPIRITS DIVISIONS table (which is not joined currently, but will be). Here's the SQL


SELECT [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].Label, [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Month End Date], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet]![Customer Group DESC] & " - " & Right([A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet]![Customer Group ID],7) AS [Customer Group], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[SPIRITS Division], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Outlet Name and Code CODE], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Outlet Name and Code DESC], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Outlet Address], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Outlet City], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Outlet Zip Code - 5], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Outlet County Desc], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Outlet State], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Spirits Segmentation Sector], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].Sector, [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[NRI Segmentation Desc], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].Segment, [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Reporting Agg Brand], IIf(IsNull([A01e OFF PREM Outlet Std Status by Agg Brand]![0_1L]),0,[A01e OFF PREM Outlet Std Status by Agg Brand]![0_1L]) AS 0_1L, IIf(IsNull([A01e OFF PREM Outlet Std Status by Agg Brand]![0_2L]),0,[A01e OFF PREM Outlet Std Status by Agg Brand]![0_2L]) AS 0_2L, IIf(IsNull([A01e OFF PREM Outlet Std Status by Agg Brand]![0_375L]),0,[A01e OFF PREM Outlet Std Status by Agg Brand]![0_375L]) AS 0_375L, IIf(IsNull([A01e OFF PREM Outlet Std Status by Agg Brand]![0_75L]),0,[A01e OFF PREM Outlet Std Status by Agg Brand]![0_75L]) AS 0_75L, IIf(IsNull([A01e OFF PREM Outlet Std Status by Agg Brand]![1_0L]),0,[A01e OFF PREM Outlet Std Status by Agg Brand]![1_0L]) AS 1_0L, IIf(IsNull([A01e OFF PREM Outlet Std Status by Agg Brand]![1_75L]),0,[A01e OFF PREM Outlet Std Status by Agg Brand]![1_75L]) AS 1_75L, IIf(IsNull([A01e OFF PREM Outlet Std Status by Agg Brand]![Total PODs]),0,[A01e OFF PREM Outlet Std Status by Agg Brand]![Total PODs]) AS [Total PODs], [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].Std, IIf(IsNull([A01e OFF PREM Outlet Std Status by Agg Brand]![Reporting Agg Brand]),"N",IIf([A01e OFF PREM Outlet Std Status by Agg Brand]![Total PODs]>=[A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet]![Std],"Y","N")) AS [Meets Std?]
FROM [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet] LEFT JOIN [A01e OFF PREM Outlet Std Status by Agg Brand] ON ([A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Outlet State] = [A01e OFF PREM Outlet Std Status by Agg Brand].[Outlet State]) AND ([A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].Sector = [A01e OFF PREM Outlet Std Status by Agg Brand].Sector) AND ([A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Reporting Agg Brand] = [A01e OFF PREM Outlet Std Status by Agg Brand].[Reporting Agg Brand]) AND ([A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[NRI Segmentation Desc] = [A01e OFF PREM Outlet Std Status by Agg Brand].[NRI Segmentation Desc]) AND ([A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Spirits Segmentation Sector] = [A01e OFF PREM Outlet Std Status by Agg Brand].[Spirits Segmentation Sector]) AND ([A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Customer Group ID] = [A01e OFF PREM Outlet Std Status by Agg Brand].[Customer Group ID]) AND ([A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet].[Outlet Name and Code CODE] = [A01e OFF PREM Outlet Std Status by Agg Brand].[Outlet Name and Code CODE]);

 

[james_lankford]

doesn't solve anything

jsut makes it easier to read

SELECT 
  [fu].Label, 
  [fu].[Month End Date], 
  [fu]![Customer Group DESC] & " - " & Right([fu]![Customer Group ID],7) AS [Customer Group], 
  [fu].[SPIRITS Division], 
  [fu].[Outlet Name and Code CODE], 
  [fu].[Outlet Name and Code DESC], 
  [fu].[Outlet Address], 
  [fu].[Outlet City], 
  [fu].[Outlet Zip Code - 5], 
  [fu].[Outlet County Desc], 
  [fu].[Outlet State], 
  [fu].[Spirits Segmentation Sector], 
  [fu].Sector, 
  [fu].[NRI Segmentation Desc], 
  [fu].Segment, 
  [fu].[Reporting Agg Brand], 
  IIf( IsNull( [ag]![0_1L] ), 0, [ag]![0_1L] ) AS 0_1L, 
  IIf( IsNull( [ag]![0_2L] ), 0, [ag]![0_2L] ) AS 0_2L, 
  IIf( IsNull( [ag]![0_375L] ), 0, [ag]![0_375L] ) AS 0_375L, 
  IIf( IsNull( [ag]![0_75L] ), 0, [ag]![0_75L] ) AS 0_75L, 
  IIf( IsNull( [ag]![1_0L] ), 0, [ag]![1_0L] ) AS 1_0L, 
  IIf( IsNull( [ag]![1_75L] ), 0, [ag]![1_75L] ) AS 1_75L, 
  IIf( IsNull( [ag]![Total PODs] ), 0, [ag]![Total PODs] ) AS [Total PODs], 
  [fu].Std, 
  IIf( IsNull( [ag]![Reporting Agg Brand]), "N", IIf( [ag]![Total PODs] >= [fu]![Std], "Y", "N" ) ) AS [Meets Std?]
FROM 
  [A00c OFF PREMISE Reporting Agg Brands by FULL UNIVERSE Outlet] as fu 
LEFT JOIN 
  [A01e OFF PREM Outlet Std Status by Agg Brand] as ag 
ON 
  (
    [fu].[Outlet State] = [ag].[Outlet State] 
  ) 
  AND 
  (
    [fu].Sector = [ag].Sector
  ) 
  AND 
  (
    [fu].[Reporting Agg Brand] = [ag].[Reporting Agg Brand] 
  ) 
  AND 
  (
    [fu].[NRI Segmentation Desc] = [ag].[NRI Segmentation Desc] 
  ) 
  AND 
  (
    [fu].[Spirits Segmentation Sector] = [ag].[Spirits Segmentation Sector] 
  ) 
  AND 
  (
    [fu].[Customer Group ID] = [ag].[Customer Group ID]
  ) 
  AND 
  (
    [fu].[Outlet Name and Code CODE] = [ag].[Outlet Name and Code CODE]
  )
;

 

[pliskers]

Thanks!

 

[Micron]

Did you post the right one? How did SPIRITS DIVISION get into this picture? I can't find a reference to [Markets] anywhere in that, nor is there a WHERE clause anywhere in it, so you can't pass a value from anything as criteria to something like this. Also, you didn't refute or confirm my assumption about running your query on each [Markets] value, so rather than beat around the bush, here's code on how to do what you originally asked for. I get the feeling you can figure out how to complete it to push to Excel. I suppose you will fire this with a button click, so either call the function with that, or paste the code into the button click event, changing ALL instances of the word "Function". Please read notes in code carefully. Hopefully you understand that the record list of Markets must be a criteria field in the query you wish to export.

As noted in the code, I wonder why you're not exporting the whole enchilada at once as I questioned in line 4 of my last post. Outputting 1 record at a time from a recordset doesn't seem efficient to me, especially if you're doing this across a network. This is 'air code' (I can't test it and it's based on info supplied). I'll be surprised if it works with no glitches at all. Not sure if you must define the parameter in the query properties as well. Good Luck!

Function RunMarketQuery
Dim rsCrit As DAO.Recordset, rsExport as DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim param As DAO.Parameter
Dim sqlFilter As String

On Error GoTo errHandler
sqlFilter = "SELECT [Market] FROM [Markets];

Set db = CurrentDb
Set qdf = db.QueryDefs("NameOfQueryToBeExported")
Set rsCrit = db.OpenRecordset(sqlFilter) 'assigned sql to variable in case it's a lot longer than I think it is
If rsCrit.RecordCount = 0 Then
  msgbox "No Market records were returned."
  Exit Function
End If

rsCrit.MoveFirst
Do While Not rsCrit.EOF
  qdf.Parameters("****") = rsCrit.Fields(0)'**** MUST be name of criteria field in NameOfQueryToBeExported
  set rsExport = qdf.OpenRecordset
    'doing msgbox if rsExport.RecordCount = 0 will hold process; decide if anything to be done
    'now do what you have to do to export this recordset. After export, we close export recordset & repeat.
    'OR append the record to a temp table and export from that table OUTSIDE of this loop.
  rsExport.Close
  rs.MoveNext
Loop

exitHere:
rsCrit.Close
Set rsCrit = Nothing
Set rsExport = Nothing

Exit Function

errHandler:
msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Function

 

[pliskers]

Thanks. Referencing MARKETS instead of SPIRITS DIVISION was my oversight. The two terms are synonymous to me but obviously you wouldn't know that.

I need to run them separately to provide the data to each division separately, as well as the fact that Access couldn't handle the number of records all at once, due to the heavy processing drain of some of the earlier queries.

Thanks for all your help, I'll give it a try.