Combining Queries

[Guest]

For some reports I have various Queries setup to collect various data. I
wanted to collect a count from each Query so I ended up creating additional
Queries for each original Query. Below is the SQL for those. This seemed to
work great but was creating many extra Queries. I wanted to combine these
into a single Query.

Taking the example below there are four original Queries and then four
additional Queries totaling eight Queries. What I want to be able to achieve
is have the four original Queries and then one additional Query for a total
of five Queries. I know the difference between Five and Eight isn't that big
but this is just an example, I actually have about a dozen different Queries
so the difference is really between Twenty-Four and Thirteen (and growing).

Is it possible to combine these Queries into one Query?

Thanks in advance for any assistance.

SQL Query One ...

SELECT Count(*) AS 0to7
FROM (SELECT DISTINCT [field]
FROM [query1]
)

SQL Query Two ...

SELECT Count(*) AS 8to14
FROM (SELECT DISTINCT [field]
FROM [query2]
)

SQL Query Three ...

SELECT Count(*) AS 15to30
FROM (SELECT DISTINCT [field]
FROM [query3]
)

SQL Query Four ...

SELECT Count(*) AS 31to42
FROM (SELECT DISTINCT [field]
FROM [query4]
)

 

[Guest]

Additional Information ...

I tried using the UNION command (see the SQL below) but unfortunately this
put the data into a single column and arranged the values smallest to
largest. It was great that it collected all the data but I was hoping to have
the values in separate columns (if you will) will the headings corresponding
to what is outlined below (not all labeled 0to7). Any thoughts?

SELECT Count(*) AS 0to7
FROM (SELECT DISTINCT [ImplementID]
FROM [REE Aging 3 Details-ARP Report 1]
)
UNION
SELECT Count(*) AS 8to14
FROM (SELECT DISTINCT [ImplementID]
FROM [REE Aging 3 Details-ARP Report 2]
)
UNION
SELECT Count(*) AS 15to30
FROM (SELECT DISTINCT [ImplementID]
FROM [REE Aging 3 Details-ARP Report 3]
)
UNION
SELECT Count(*) AS 31to42
FROM (SELECT DISTINCT [ImplementID]
FROM [REE Aging 3 Details-ARP Report 4]
)

 

[Jeff Boyce]

Frank

Have you looked into using a cross-tab query?

Have you looked into using a Totals query?

Have you considered using the grouping features of Reports to do this?

Good luck

Jeff Boyce
<Access MVP>