Complex SQL

[JustinP]

I need to make a query for a report that includes a number of count and
count-like statements.

As an example (there are about 20 other statements like this example in
the query):

SELECT
Abs(Sum((IIf(MasterTable.SECTION_NAME="BCab",1,0)) And
(IIf(MasterTable.Private_Gov="PRIVATE",1,0)) And
(IIf(MasterTable.REDUNDANT=No,1,0)))) AS CountOfPrivOwn
FROM MasterTable;

but I also need to include this (and several like this with other
criteria):

SELECT Count(*) FROM
(SELECT DISTINCT Owner FROM Mastertable);


Is it possible to include both of these types of statements in the one
query? (I want this in one query for convience more than anything. Is
it possible to programmatically call data from another query in Access?
This may be an option.)

I am aware I could use a UNION in between them but as this would put
the second type of statement on another line could this data then be
automatically taken into the report?

Are there any other ways around this? Your suggestions are most welcome.

 

[Douglas J. Steele]

What, in English, are you expecting

SELECT
Abs(Sum((IIf(MasterTable.SECTION_NAME="BCab",1,0)) And
(IIf(MasterTable.Private_Gov="PRIVATE",1,0)) And
(IIf(MasterTable.REDUNDANT=No,1,0)))) AS CountOfPrivOwn
FROM MasterTable;

to be doing?

Assuming you want to count all those records where SECTION_NAME = BCab AND
Private_Got = PRIVATE AND Redundant = No, you'd be best off ANDing the
conditions together, not the results of the individual IIf statements:

SELECT
Abs(Sum((IIf(MasterTable.SECTION_NAME="BCab" And
(MasterTable.Private_Gov="PRIVATE" And MasterTable.REDUNDANT=No,1,0))) AS
CountOfPrivOwn
FROM MasterTable;

What I've often done when I needed to UNION various counting queries
together was then do a SUM query on the resultant query.

In other words, I might have Query1 as

SELECT Sum(Field1) AS Result1, 0 AS Result2
FROM Table1
UNION
SELECT 0, Sum(Field2)
FROM Table2

and then

SELECT Sum(Result1) AS FirstSum, Sum(Result2) AS SecondSum
FROM Query1

so that everything appears as a single row.