Query output to another query

[Mem Phiz]

I want to use the output of one query and use it in another query. How
can I do so in Access 2007?
I will be filtering some records based on the Max(date) in the 1st
query. Only the records which have the latest date will be fed in to
the 2nd query.

I had tried combining all the queries into one big query, but it
didn't work. The only way it would work is to split the max date
filtering into a separate Query1 and then run the other part in
Query2.

I really appreciate your time and thank you in advance for all your
help.
Regards,
M

 

[John Spencer]

You can use sub-queries in the where clause. Since you did not post the SQL
of the two queries it is not possible to give you a detailed response.

The simplest example would be the following.
SELECT *
FROM TableTWO
WHERE TableTwo.DateField =
(SELECT MAX(TableOne.DateField)
FROM TableOne)

You can also use a sub-query in the FROM clause of a query
SELECT TableTwo.*
FROM TableTwo INNER JOIN
(SELECT ProductID, Max(DateSold) as LastDate
FROM TableOne
GROUP BY ProductID) as qLastDate
ON TableTwo.ProductID = qLastDate.ProductID
AND TableTwo.DateField = qLastDate.LastDate

You can't use the above construct as a saved query if your table names or
field name do not follow the naming convention - Only letters, numbers, and
underscore characters allowed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County