Query to complex for simple UNION query... (1 Viewer)

[MSherfey]

I have, what I think, is a basic union query:

SELECT [Breakdown Union xFY].ENDUSER_CUST_NUM, Sum([Breakdown Union xFY].[FY 2009 Covered]) AS [Product Value Sum], "FY09" AS [FY Covered]
FROM [Breakdown Union xFY]
GROUP BY [Breakdown Union xFY].ENDUSER_CUST_NUM, "FY09"
UNION
SELECT [Breakdown Union xFY].ENDUSER_CUST_NUM, Sum([Breakdown Union xFY].[FY09-Q1 Covered]) AS [Product Value Sum], "FY09-Q1" AS [FY Covered]
FROM [Breakdown Union xFY]
GROUP BY [Breakdown Union xFY].ENDUSER_CUST_NUM, "FY09-Q1"
UNION
SELECT [Breakdown Union xFY].ENDUSER_CUST_NUM, Sum([Breakdown Union xFY].[FY09-Q2 Covered]) AS [Product Value Sum], "FY09-Q2" AS [FY Covered]
FROM [Breakdown Union xFY]
GROUP BY [Breakdown Union xFY].ENDUSER_CUST_NUM, "FY09-Q2"
UNION
SELECT [Breakdown Union xFY].ENDUSER_CUST_NUM, Sum([Breakdown Union xFY].[FY09-Q3 Covered]) AS [Product Value Sum], "FY09-Q3" AS [FY Covered]
FROM [Breakdown Union xFY]
GROUP BY [Breakdown Union xFY].ENDUSER_CUST_NUM, "FY09-Q3";

However, it is saying it is too complex...whatever that means. If I remove the last UNION section and stop at FY09-Q2 it works fine. I'm not sure why adding next section would make it too complex.

In the end I want to add the same structure for FY10 and FY11 in the same query.

Ideas?

 

[gemma-the-husky]

too complex means access can't manage to construct a query with all these relations.

try this - construct each bit separately

the have the union query do this

select * from query1 union
select * from query2 union
select * from query3 etc

see if that works


otherwise you might need another technique. eg, use the queries to append to a pre-defined table, and then use the final table for your process

 

[MSherfey]

So, just to make sure I understand. If I have everything in one UNION query, Access freaks out because there's too much going on at one time. However, if I make separate queries, Access can run a query, 'save' the results, and then run the next query thus saving its mental state I was hoping to not have to create 15 separate queries for this but at least it will run. Thanks!