combining two SQL SELECT COUNT(*) statements

Question

I'm using Microsoft Access 2007. I have two SELECT COUNT(*) statements which run ok without combining, but I want to combine those statements to use as a form recordsource via VBA.

1. SELECT Count(*) AS OrderCount FROM (SELECT DISTINCT OrderNo FROM tblDisposition);"

2. SELECT Count(*) AS ReviewCount FROM tblDisposition WHERE [ReviewClose]=0;

I know there are already lot of this kind of question such as :

two SQL COUNT() queries?

Access 2007: "SELECT COUNT(DISCTINCT ..."

How do I combine 2 select statements into one?

and many more. Here's what I've tried but still failed:

SELECT (SELECT Count(*) AS OrderCount FROM (SELECT DISTINCT OrderNo FROM tblDisposition)),"
        (SELECT Count(*) AS ReviewCount FROM tblDisposition WHERE [ReviewClose]=0);"

I set the combined SQL statement on myForm as recordsource and I have two textfields which I set the controlsource as OrderCount and ReviewCount. On form load event which I set the recorsource, both textfields give #NAME error but if I set only either one SQL statement, it will display the correct count on the textfield.

Solution 1

You can get both counts as two values in a single row using the following SQL statement:

SELECT 
    (
        SELECT Count(*) AS OrderCount 
        FROM 
            (
                SELECT DISTINCT OrderNo FROM tblDisposition
            )
    ) AS OrderCount,
    (
        SELECT Count(*) AS ReviewCount 
        FROM tblDisposition 
        WHERE [ReviewClose]=0
    ) AS ReviewCount
FROM
    (
        SELECT COUNT(*) FROM tblDisposition
    )

Note that the final FROM clause is really just a way to get the query to return a single row. This could also have been done with a Dual table, described here.

Solution 2

I suppose your query isn't working at all. Try adding '* FROM' like this:

SELECT * FROM 
(SELECT Count(*) AS OrderCount FROM (SELECT DISTINCT OrderNo FROM tblDisposition)),
(SELECT Count(*) AS ReviewCount FROM tblDisposition WHERE [ReviewClose]=0)