Embed a query in a query (ie merge 2 queries into 1)

[macroapa]

HI have the following query (A) below and then query (B) which runs on
querys (A). What I would like to do is merge the 2 into 1. Is this
possible?

Thanks.

(A) - called CSRAvgStepTouc1

SELECT tblTouches08ProdAPA.level7, tblTouches08ProdAPA.workitemstepid,
tblTouches08ProdAPA.stepname, Count(tblTouches08ProdAPA.duration) AS
CountOfduration1
FROM tblTouches08ProdAPA
WHERE (((tblTouches08ProdAPA.TouchStart)<#11/1/2008#))
GROUP BY tblTouches08ProdAPA.level7,
tblTouches08ProdAPA.workitemstepid, tblTouches08ProdAPA.stepname
HAVING (((tblTouches08ProdAPA.level7)="Ray, Ray"))
ORDER BY tblTouches08ProdAPA.workitemstepid,
tblTouches08ProdAPA.stepname;

(B)

SELECT CSRAvgStepTouc1.stepname, Avg(CSRAvgStepTouc1.CountOfduration1)
AS AvgOfCountOfduration1
FROM CSRAvgStepTouc1
GROUP BY tblTouches08ProdAPA.level7,
tblTouches08ProdAPA.workitemstepid, tblTouches08ProdAPA.stepname
HAVING (((tblTouches08ProdAPA.level7)="Ray, Robin"))
ORDER BY tblTouches08ProdAPA.workitemstepid,
tblTouches08ProdAPA.stepname
GROUP BY CSRAvgStepTouc1.stepname;

 

[BruceM]

This discussion of subqueries may help:
http://allenbrowne.com/subquery-01.html

 

[John Spencer]

If your Table and field names follow the conventions of consisting of only
letters, numbers and the underscore character you can do this.

The simplest method is a copy and paste: Copy all the text of the first query
LESS the semi-colon at the end and if there is an ORDER BY clause that can
also be dropped.

In the second query just before the query name type " ( ) AS "
And then paste the first query between the parentheses

(A) - called CSRAvgStepTouc1

SELECT tblTouches08ProdAPA.level7, tblTouches08ProdAPA.workitemstepid,
tblTouches08ProdAPA.stepname, Count(tblTouches08ProdAPA.duration) AS
CountOfduration1
FROM tblTouches08ProdAPA
WHERE (((tblTouches08ProdAPA.TouchStart)<#11/1/2008#))
GROUP BY tblTouches08ProdAPA.level7,
tblTouches08ProdAPA.workitemstepid, tblTouches08ProdAPA.stepname
HAVING (((tblTouches08ProdAPA.level7)="Ray, Ray"))
ORDER BY tblTouches08ProdAPA.workitemstepid,
tblTouches08ProdAPA.stepname;

(B)

SELECT CSRAvgStepTouc1.stepname, Avg(CSRAvgStepTouc1.CountOfduration1)
AS AvgOfCountOfduration1
FROM

(SELECT tblTouches08ProdAPA.level7, tblTouches08ProdAPA.workitemstepid,
tblTouches08ProdAPA.stepname, Count(tblTouches08ProdAPA.duration) AS
CountOfduration1
FROM tblTouches08ProdAPA
WHERE (((tblTouches08ProdAPA.TouchStart)<#11/1/2008#))
GROUP BY tblTouches08ProdAPA.level7,
tblTouches08ProdAPA.workitemstepid, tblTouches08ProdAPA.stepname
HAVING (((tblTouches08ProdAPA.level7)="Ray, Ray")) ) AS CSRAvgStepTouc1

GROUP BY tblTouches08ProdAPA.level7,
tblTouches08ProdAPA.workitemstepid, tblTouches08ProdAPA.stepname
HAVING (((tblTouches08ProdAPA.level7)="Ray, Robin"))
ORDER BY tblTouches08ProdAPA.workitemstepid,
tblTouches08ProdAPA.stepname
GROUP BY CSRAvgStepTouc1.stepname;

You can remove the ORDER BY clause since it will have no real effect. One
limitation in Access is that a subquery in the FROM clause CANNOT use any
square brackets. If it does, the query will error.

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