0

How can I select count(*) from two different tables (table1 and table2) having as result:

Count_1   Count_2
123       456

I've tried this:

select count(*) as Count_1 from table1 
UNION select count(*) as Count_2 from table2;

But here's what I get:

Count_1
123
456

I can see a solution for Oracle and SQL server here, but either syntax doesn't work for MS Access (I am using Access 2013). Select count(*) from multiple tables

I would prefer to do this using SQL (because I am developing my query dynamically within VBA).

Community
  • 1
  • 1
Dr Phil
  • 430
  • 5
  • 17
  • you should have a look into Access TRANSFORM PIVOT. – mxix Jan 22 '16 at 19:25
  • Thank you @mxix, that sounds promising. I want to give it a closer look, but for now Horaciux's response below gets me exactly what I was looking for. – Dr Phil Jan 22 '16 at 20:15

3 Answers3

4

Cross join two subqueries which return the separate counts:

SELECT sub1.Count_1, sub2.Count_2
FROM
    (SELECT Count(*) AS Count_1 FROM table1) AS sub1,
    (SELECT Count(*) AS Count_2 FROM table2) AS sub2;
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • A comment disappeared, but thanks for the suggestion to change `SELECT *` to `SELECT sub1.Count_1, sub2.Count_2` – HansUp Jan 22 '16 at 20:29
3
    Select TOP 1
     (Select count(*) as Count from table1) as count_1, 
     (select count(*) as Count from table2) as count_2
   From table1
Horaciux
  • 6,322
  • 2
  • 22
  • 41
  • Thanks @Horaciux, this one gives me an error message "Query input must contain at least one table or query." – Dr Phil Jan 22 '16 at 19:44
  • http://stackoverflow.com/questions/7933518/table-less-union-query-in-ms-access-jet-ace – Horaciux Jan 22 '16 at 19:49
  • Take a look at the link. It mentions how to manage this scenario. See if my edit work, I don't have access here – Horaciux Jan 22 '16 at 19:50
  • Thank you @Horaciux, that does work. I actually took out "as Count" from the middle because I didn't think that's needed. – Dr Phil Jan 22 '16 at 20:07
0

Is a SUM (or AVG) like this what you are trying to achieve?

select sum(count) from (
select count(*) as Count from table1 
UNION select count(*) as Count from table2
);
Javier
  • 2,093
  • 35
  • 50