A union B works, A union B union C fails

[Phil Smith]

Each individual query is fairly simple. One table has fields for three
possible sales reps, I need to treat them as as one set of fields.

Each query is identical, except for the three fields that refer to Rep1,
Rep2, and Rep3.

A, B, or C run fine seperately.
A union B
B union C
A Union C
all run fine.

A Union B Union C fails with an ODBC error.

I can fudge it with make table/append queries, but any ideas?

Thanx

 

[John Vinson]

Each individual query is fairly simple. One table has fields for three
possible sales reps, I need to treat them as as one set of fields.

Then it's an incorrectly normalized table... I presume you're trying
to fix that problem.

Each query is identical, except for the three fields that refer to Rep1,
Rep2, and Rep3.

A, B, or C run fine seperately.
A union B
B union C
A Union C
all run fine.

A Union B Union C fails with an ODBC error.

ODBC... what's the actual data storage database? Are these linked
tables in Access? What's the actual error message?

I can fudge it with make table/append queries, but any ideas?

Please post your SQL.

John W. Vinson[MVP]

 

[Phil Smith]

Thanx for the response.
I am not trying to fix it as much as deal with it. It is fairly
normalized, as these three fields are different: For instance, Rep1 is
paid a commision based on the sale, while Rep2 is paid upon payment of
invoice...However, I can not change the structure, so I am stuck with it.

Anywho, this is housed in a MYSQL database, and the error I am getting is:
ODBC --call failed (error 3146)

Here is the query that fails. Remove any section, and it works fine.

SELECT
[invoice_d]![ship_qty]*[invoice_d]![price]*[invoice_h]![sales_rep1pct]/100
AS Ext_Commision, invoice_h.sales_rep1pct, invoice_h.sales_rep1comm,
invoice_h.sales_rep1type
FROM customer INNER JOIN (invoice_d INNER JOIN invoice_h ON
(invoice_d.company_id = invoice_h.company_id) AND (invoice_d.invoice_id
= invoice_h.invoice_id)) ON customer.customer_id = invoice_h.customer_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_h.status)=8 Or (invoice_h.status)=9));
Union

SELECT
[invoice_d]![ship_qty]*[invoice_d]![price]*[invoice_h]![sales_rep2pct]/100
AS Ext_Commision, invoice_h.sales_rep2pct, invoice_h.sales_rep2comm,
invoice_h.sales_rep2type
FROM customer INNER JOIN (invoice_d INNER JOIN invoice_h ON
(invoice_d.company_id = invoice_h.company_id) AND (invoice_d.invoice_id
= invoice_h.invoice_id)) ON customer.customer_id = invoice_h.customer_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_h.status)=8 Or (invoice_h.status)=9));
UNION

SELECT invoice_d!ship_qty*invoice_d!price*invoice_h!sales_rep3pct/100
AS Ext_Commision, invoice_h.sales_rep3pct, invoice_h.sales_rep3comm,
invoice_h.sales_rep3type
FROM customer INNER JOIN (invoice_d INNER JOIN invoice_h ON
(invoice_d.invoice_id = invoice_h.invoice_id) AND (invoice_d.company_id
= invoice_h.company_id)) ON customer.customer_id = invoice_h.customer_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_h.status)=8 Or (invoice_h.status)=9));