CODEWITHSUNDEEP
sqlms-access
Dr Phil
Dr Phil

Reputation: 807

Select count(*) from multiple tables in MS Access

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).

Upvotes: 0

Views: 3850

Answers (3)

HansUp
HansUp

Reputation: 97101

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;

Upvotes: 4

Horaciux
Horaciux

Reputation: 6477

    Select TOP 1
     (Select count(*) as Count from table1) as count_1, 
     (select count(*) as Count from table2) as count_2
   From table1

Upvotes: 3

Javier
Javier

Reputation: 2159

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
);

Upvotes: 0

Related Questions