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.

@AmitSingh i set the combined SQL statement on myForm. and i have two textfields which i set the controlsource as OrderCount and ReviewCount. when runs,both textfields give #NAME error. but if i set only either one SQL statement,it will display the correct count on the textfield. — mat duwe
– mat duwe, Commented Jun 5, 2013 at 9:40

Community · Accepted Answer · 2017-05-23 11:59:46Z

3

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.

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Jun 6, 2013 at 10:29

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikhail · Accepted Answer · 2013-06-05 10:12:39Z

1

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)

answered Jun 5, 2013 at 10:12

Mikhail

1,5702 gold badges13 silver badges13 bronze badges

2 Comments

mat duwe Over a year ago

using this,now i get the 'run-time error 3074: cannot repeat table name '%$##@_Alias' in FROM clause'

Mikhail Over a year ago

Waht aliases are you using? office.microsoft.com/en-us/access-help/HV080760180.aspx Try adding different.

Collectives™ on Stack Overflow

combining two SQL SELECT COUNT(*) statements

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related