SELECT without FROM Clause

Question

I'm writing a SQL statement for my MS Access database, and the purpose is to count values from 3 different queries, so I tried this way:

SELECT(query1 + query2 + query3) AS Qtd

Each query returns an unique value from an aggregate function count, i.e, query1 = SELECT Count(something) FROM Table WHERE...

Everything should work fine, but MS Access demands a FROM clause. When I put a table in that query (not changing the SELECT statement above), I end up with tones of rows and each row the result expected from Qtd column.

So is there any way to skip the FROM Clause or the only option to work around is write TOP 1 (or DISTINCT) to not get tones of duplicated rows because of the unnecessary table in FROM clause?

Melessa

Answer 1

Consider the cross join (comma separated tables) of the aggregate queries:

SELECT (query1.CntColumn + query2.CntColumn + query3.CntColumn) AS Qtd
FROM query1, query2, query3

Afterworld

This solution worked fine and seemed to me the simplest one. Thank you! – Melessa

Answer 2

You could union all the queries and then sum all the results:

SELECT SUM(cnt)
FROM   (SELECT COUNT(*) AS cnt FROM table1 WHERE ...
        UNION ALL
        SELECT COUNT(*) AS cnt FROM table2 WHERE ...
        -- Etc..
       ) t

Ramberg

I tried to run in MS Access and I got "Query is to Complex" – Melessa

Please post the underlying SQL statement of queries as a calculated column, reserved word, data type handling could be the issue. – Afterworld