Access 2007 iterate through a saved query

Question

I am working with a fairly large database of information. I need to use a distinct set of values in one saved query as the parameter in the 'where' clause for another query. What is the best way to iterate through the result set of distinct values? I am fairly new to Access and VBA.

Answer 1

is it a passthrough query or another Access query? If passthrough, use VBA to replace the 2nd query's .sql property with values from the 1st query. If Access, you can probably use a join.

Answer 2

@Beth's answer is probably the best and if you wish to post some details of your tables and queries, I am sure a more detailed answer can be provided, however, if you need to iterate through the query results for some reason other than to obtain a second result set, you can use a recordset. In VBA:

 Dim rs As DAO.Recordset
 Dim rs2 As DAO.Recordset
 Dim sSQL As String

 ''You open an SQL string, a table, or a query as a recordset
 Set rs = CurrentDB.OpenRecordset("QueryName")

 Do While Not rs.EOF
    sSQL= "SELECT Some, Fields FROM ATable WHERE ID = " & rs!NumericID

    Set rs2 = CurrentDB.OpenRecordset(sSQL)
    ''Now what?
    rs.MoveNext
 Loop

Answer 3

I may not understand the question, because I'm interpreting it completely differently from the other answers.

It seems to me that the others have answered assuming that "What is the best way to iterate through the result set of distinct values" is the key part, where I'm concentrating on "I need to use a distinct set of values in one saved query as the parameter in the 'where' clause for another query".

There are two ways to approach this:

1. the standard Access way: save the other query, add it to the first query and join on the field that you're filtering on.

2. use the IN clause as a subquery.

Say your main query is:

  SELECT tblBook.BookID, tblBook.AuthorID, tblBook.Title
  FROM tblBook

...and you want to filter to a set of authors using this query:

  SELECT tblAuthor.AuthorID
  FROM tblAuthor
  WHERE tblAuthor.BirthYear < 1900

You could save that last query as, say "qryAuthorsBefore1900" and then use it in a join in the first query:

  SELECT tblBook.BookID, tblBook.AuthorID, tblBook.Title
  FROM tblBook INNER JOIN qryAuthorsBefore1900 ON tblBook.AuthorID = qryAuthorsBefore1900.AuthorID

That's method 1.

Method 2 would be:

  SELECT tblBook.BookID, tblBook.AuthorID, tblBook.Title
  FROM tblBook
  WHERE tblBook.AuthorID IN (SELECT tblAuthor.AuthorID FROM tblAuthor WHERE tblAuthor.BirthYear < 1900)

Now, both of these are unneeded -- you could do the whole thing without a subquery or a saved QueryDef:

  SELECT tblBook.BookID, tblBook.AuthorID, tblBook.Title
  FROM tblBook INNER JOIN tblAuthor ON tblBook.AuthorID = tblAuthor.AuthorID
  WHERE tblAuthor.BirthYear < 1900

This result set should be editable.

All of the queries above can be created using the regular Query By Example grid, no need to dip into SQL view (except for copying the SQL for the subquery, if you decide to go that route).

But, of course, I may have completely misinterpreted the entire question.