3

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.

Community
  • 1
  • 1
mat duwe
  • 153
  • 1
  • 7
  • 18
  • @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 Jun 05 '13 at 09:40
  • provide column alias name and than try – Amit Singh Jun 05 '13 at 09:43

2 Answers2

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.

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
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)
Mikhail
  • 1,540
  • 2
  • 13
  • 13
  • using this,now i get the 'run-time error 3074: cannot repeat table name '%$##@_Alias' in FROM clause' – mat duwe Jun 06 '13 at 00:17
  • Waht aliases are you using? http://office.microsoft.com/en-us/access-help/HV080760180.aspx Try adding different. – Mikhail Jun 06 '13 at 08:27