query rewrites itself and throws error

[dharkangel]

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?

 

[lameid]

Hmm... Square brackets [] denote an object name or parameter... Objects can only be 255 characters long, Access must be truncating it. Try parenthesis () instead.

Although, I'm not sure Access even supports subqueries in the from clause. I think you'd have to use a query for each Select statement and base each select off of the previous query.

 

[stix4t2]

You could consider adding a date to the tables and combining all the tables into one. Then you wouldn't have to create code like this.

Also instead of a sql statement, you may want to use vba and the DCount method to get the total.