CODEWITHSUNDEEP
sqlselectcountms-access-2007
Jon Rubins
Jon Rubins

Reputation: 4413

SELECT with COUNT from multiple tables in access 2007

I have 2 queries called "3 Month Query" and "6 Month Query". Now I'm creating a report with a query as a record source. I want to count the number of rows in 3 Month Query and label it as Need3Month, and the number of rows in 6 Month Query and label it as Need6Month. I tried the following which is not working:

SELECT COUNT([3 Month Query].[ID]) AS Need3Month, COUNT([6 Month Query].[ID]) AS Need6Month FROM [3 Month Query], [6 Month Query];

And I also tried:

SELECT COUNT([3 Month Query].[ID] AS Need3Month FROM [3 Month Query]
UNION
SELECT COUNT([6 Month Query].[ID] AS Need6Month FROM [6 Month Query];

This returns the right numbers but lists them both under a Need3Month column.

What am I supposed to do? Any help is much appreciated.

Upvotes: 2

Views: 4343

Answers (2)

Andriy M
Andriy M

Reputation: 77677

SELECT
  (SELECT COUNT([ID]) FROM [3 Month Query]) AS Need3Month,
  (SELECT COUNT([ID]) FROM [6 Month Query]) AS Need6Month

UPDATE in reply to comments:

SELECT
  COUNT([ID]) AS Need3Month,
  (SELECT COUNT([ID]) FROM [6 Month Query]) AS Need6Month
FROM [3 Month Query]

UPDATE 2

This should also work (I think):

SELECT
  Cnt6.Cnt AS Need3Month,
  Cnt3.Cnt AS Need3Month
FROM
  (SELECT COUNT([ID]) AS Cnt FROM [3 Month Query]) AS Cnt3,
  (SELECT COUNT([ID]) AS Cnt FROM [6 Month Query]) AS Cnt6

Upvotes: 3

Chris Foster
Chris Foster

Reputation: 1300

Not sure if it works in MS Access but in SQL you would do something like this:

SELECT 
    (SELECT COUNT(ID) FROM [3 Month Query]) Need3Month, 
    (SELECT COUNT(ID) FROM [6 Month Query]) Need6Month

Upvotes: 3

Related Questions