title: combining two SQL SELECT COUNT(*) statements
body: <p>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. </p>
  
  <pre><code>1. SELECT Count(*) AS OrderCount FROM (SELECT DISTINCT OrderNo FROM tblDisposition);"
  
  2. SELECT Count(*) AS ReviewCount FROM tblDisposition WHERE [ReviewClose]=0;
  </code></pre>
  
  <p>I know there are already lot of this kind of question such as :</p>
  
  <p><a href="https://stackoverflow.com/questions/888731/two-sql-count-queries">two SQL COUNT() queries?</a></p>
  
  <p><a href="https://stackoverflow.com/questions/1330639/access-2007-select-countdisctinct">Access 2007: &quot;SELECT COUNT(DISCTINCT ...&quot;</a></p>
  
  <p><a href="https://stackoverflow.com/questions/542705/how-do-i-combine-2-select-statements-into-one">How do I combine 2 select statements into one?</a> </p>
  
  <p>and many more. Here's what I've tried but still failed: </p>
  
  <pre><code>SELECT (SELECT Count(*) AS OrderCount FROM (SELECT DISTINCT OrderNo FROM tblDisposition)),"
          (SELECT Count(*) AS ReviewCount FROM tblDisposition WHERE [ReviewClose]=0);"
  </code></pre>
  
  <p>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.</p>