query re-writes itself and throws error

[dhx10000]

Hi,

I have this query here:

SELECT sum(subtotal) AS final_count
FROM [select count(*) as subtotal from 1_2_06 WHERE Role='EUM' union all select count(*) from 1_9_06 WHERE Role='EUM' union all select count(*) from 1_16_06 WHERE Role='EUM' union all select count(*) from 1_23_06 WHERE Role='EUM' union all select count(*) from 1_30_06 union all select count(*) from 2_6_06 WHERE Role='EUM' union all select count(*) from 2_13_06 WHERE Role='EUM' union all select count(*) from 2_20_06 WHERE Role='EUM' union all select count(*) from 2_27_06 WHERE Role='EUM']. AS all_counts;

I save it as a query then I try and run it, but I get an error saying that the path is invalide. When I open the query to look at the SQL it looks like this:

SELECT sum(subtotal) AS final_count
FROM [select count(*) as subtotal from 1_2_06 WHERE Role='EUM' union all select count(*) from 1_9_06 WHERE Role='EUM' union all select count(*) from 1_16_06 WHERE Role='EUM' union all select count(*) from 1_23_06 WHERE Role='EUM' union all select count(*) fro] AS all_counts;

Why is this happening?

 

[Pat Hartman]

Remove the square brackets from the original query.

Looks like you have a design problem if you have separate tables for each day and if these are queries, then you have over complicated the counting process.