Multiple queries in a union query

[accessaspire219]

I was wondering if it is possible to have multiple queries within a union query? I tried to do that by extending the SQL for union of two queries but it did not work. I want to determine is I have the SQL code wrong of if there is a limitation in access that prevents making a union query that is a union of more than 2 queries.
Anyone?

 

[pbaldy]

A UNION query is by definition a combination of more than one query. Post the SQL of your effort and we'll sort it out.

 

[accessaspire219]

SELECT
Calc_buyer_MIOH_by_period.[ABUYR],
Calc_buyer_MIOH_by_period.[PERIOD1],
Calc_buyer_MIOH_by_period.[METRIC],
Calc_buyer_MIOH_by_period.[TYPE],
FROM Calc_buyer_MIOH_by_period
UNION SELECT
Calc_buyer_MIOH_by_quarter.[ABUYR],
Calc_buyer_MIOH_by_quarter.[PERIOD1],
Calc_buyer_MIOH_by_quarter.[METRIC],
Calc_buyer_MIOH_by_quarter.[TYPE],
FROM Calc_buyer_MIOH_by_quarter
UNION SELECT
Calc_buyer_MIOH_by_year.[ABUYR],
Calc_buyer_MIOH_by_year.[PERIOD1],
Calc_buyer_MIOH_by_year.[METRIC],
Calc_buyer_MIOH_by_year.[TYPE],
FROM Calc_buyer_MIOH_by_year
UNION SELECT
Calc_buyer_service_by_period.[ABUYR],
Calc_buyer_service_by_period.[PERIOD1],
Calc_buyer_service_by_period.[METRIC],
Calc_buyer_service_by_period.[TYPE],
FROM Calc_service_service_by_period
UNION SELECT
Calc_buyer_service_by_quarter.[ABUYR],
Calc_buyer_service_by_quarter.[PERIOD1],
Calc_buyer_service_by_quarter.[METRIC],
Calc_buyer_service_by_quarter.[TYPE],
FROM Calc_service_service_by_quarter
UNION SELECT
Calc_buyer_service_by_year.[ABUYR],
Calc_buyer_service_by_year.[PERIOD1],
Calc_buyer_service_by_year.[METRIC],
Calc_buyer_service_by_year.[TYPE],
FROM Calc_service_service_by_year
UNION SELECT
Calc_buyer_surplus_by_period.[ABUYR],
Calc_buyer_surplus_by_period.[PERIOD1],
Calc_buyer_surplus_by_period.[METRIC],
Calc_buyer_surplus_by_period.[TYPE],
FROM Calc_surplus_by_period
UNION SELECT
Calc_buyer_surplus_by_quarter.[ABUYR],
Calc_buyer_surplus_by_quarter.[PERIOD1],
Calc_buyer_surplus_by_quarter.[METRIC],
Calc_buyer_surplus_by_quarter.[TYPE],
FROM Calc_surplus_by_quarter
UNION SELECT
Calc_buyer_surplus_by_year.[ABUYR],
Calc_buyer_surplus_by_year.[PERIOD1],
Calc_buyer_surplus_by_year.[METRIC],
Calc_buyer_surplus_by_year.[TYPE],
FROM Calc_surplus_by_year;

 

[pbaldy]

The commas between the last field in each SELECT clause and the FROM clause are just as invalid in a UNION query as they would be in a "regular" query. Remove those and see where we're at.

 

[accessaspire219]

Thanks pbaldy got that out of the way. I am getting a division by zero error. Do you know what could be causing that?

 

[accessaspire219]

actually its a dialog box saying Division by zero

 

[pbaldy]

I don't see anything there that would cause that. Could it be coming from one of the underlying queries (IOW, is there division happening there)?

 

[MSAccessRookie]

actually its a dialog box saying Division by zero

The UNION Query code that you posted above (corrected to remove the commas as pbaldy suggested) should not be creating that error. Does it seem like it is creating the error condition? If not, then I think we need more information. If so, then since these look like joined Queries, perhaps one of them can be looked at for the answer.

NOTE: Looks Like pbaldy said the same thing in a different way. Take a look at any other Queries that are involved.

 

[accessaspire219]

one of the queries containers a #error. Could that be the cause? How do I avoid the #error?

 

[pbaldy]

A typical solution to the division by zero error is something like this in the query:

IIf(Nz(FieldName, 0) = 0, 0, OtherField/FieldName)