CODEWITHSUNDEEP
sqlms-accessms-access-2007vba
mat duwe
mat duwe

Reputation: 153

combining two SQL SELECT COUNT(*) statements

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.

Upvotes: 3

Views: 12711

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123474

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.

Upvotes: 3

Mikhail
Mikhail

Reputation: 1560

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)

Upvotes: 1

Related Questions