Another query too complex problem

P

Phil Smith

I am really really over this garbage. I have a system that has been
running fine for a couple of YEARS, and suddenly I start getting query
too complex, and as usual, it makes no sense!

I have a union query, which works PERFECTLY.
If I create a query to reference that union query, that query gets "Too
Complex."

Even if all I do is choose a single field, no criteria, no nothing:

SELECT SalesCombinedLicensee.invoice_date
FROM SalesCombinedLicensee;

SalesCombinedLicensee works perfectly fine. Why would THIS query result
in "query too cmplex?!!!!!!!!!

The query which works is as follows:


SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id
FROM territory INNER JOIN (customer INNER JOIN (((item_types INNER JOIN
(licensor INNER JOIN item ON licensor.licensor_id =
item.pri_licensor_id) ON item_types.type_id = item.item_type) INNER JOIN
invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN invoice_h ON
invoice_d.invoice_id = invoice_h.invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_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) AND
((licensor.labels) Is Not Null And (licensor.labels)>""));


UNION ALL SELECT invoice_h.invoice_date, invoice_h.status,
licensor.labels, item_types.name, territory.name,
IIf(territory!name="Foreign_1" Or territory!name="Foreign_2" Or
territory!name="Foreign_3","YES","NO") AS [Foreign],
invoice_d!ship_qty*invoice_d!price AS Ext_price, invoice_d.ship_qty,
invoice_d.item_id
FROM licensor INNER JOIN (territory INNER JOIN (customer INNER JOIN
(((item_types INNER JOIN item ON item_types.type_id = item.item_type)
INNER JOIN invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON territory.territory_id
= customer.territory_id) ON licensor.licensor_id = item.sec_licensor_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) AND ((licensor.labels) Is
Not Null And (licensor.labels)>""));
 
K

kc-mass

Hi Phil,

When I've run into that (in a previously running query) it has always been
because of "bad" data. After all that is what has changed. To test, limit
your
data to a small set that you know has no unwanted Nulls et al.

Regards

Kevin

Phil Smith said:
I am really really over this garbage. I have a system that has been
running fine for a couple of YEARS, and suddenly I start getting query too
complex, and as usual, it makes no sense!

I have a union query, which works PERFECTLY.
If I create a query to reference that union query, that query gets "Too
Complex."

Even if all I do is choose a single field, no criteria, no nothing:

SELECT SalesCombinedLicensee.invoice_date
FROM SalesCombinedLicensee;

SalesCombinedLicensee works perfectly fine. Why would THIS query result
in "query too cmplex?!!!!!!!!!

The query which works is as follows:


SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id
FROM territory INNER JOIN (customer INNER JOIN (((item_types INNER JOIN
(licensor INNER JOIN item ON licensor.licensor_id = item.pri_licensor_id)
ON item_types.type_id = item.item_type) INNER JOIN invoice_d ON
item.item_id = invoice_d.item_id) INNER JOIN invoice_h ON
invoice_d.invoice_id = invoice_h.invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_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) AND ((licensor.labels) Is
Not Null And (licensor.labels)>""));


UNION ALL SELECT invoice_h.invoice_date, invoice_h.status,
licensor.labels, item_types.name, territory.name,
IIf(territory!name="Foreign_1" Or territory!name="Foreign_2" Or
territory!name="Foreign_3","YES","NO") AS [Foreign],
invoice_d!ship_qty*invoice_d!price AS Ext_price, invoice_d.ship_qty,
invoice_d.item_id
FROM licensor INNER JOIN (territory INNER JOIN (customer INNER JOIN
(((item_types INNER JOIN item ON item_types.type_id = item.item_type)
INNER JOIN invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON territory.territory_id =
customer.territory_id) ON licensor.licensor_id = item.sec_licensor_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) AND ((licensor.labels) Is
Not Null And (licensor.labels)>""));
Click to expand...
 
P

Phil Smith

Nope, that's not it. I used last years data, (a set which does not
change anymore, and I get the same results.


Hi Phil,

When I've run into that (in a previously running query) it has always been
because of "bad" data. After all that is what has changed. To test, limit
your
data to a small set that you know has no unwanted Nulls et al.

Regards

Kevin

Phil Smith said:
I am really really over this garbage. I have a system that has been
running fine for a couple of YEARS, and suddenly I start getting query too
complex, and as usual, it makes no sense!

I have a union query, which works PERFECTLY.
If I create a query to reference that union query, that query gets "Too
Complex."

Even if all I do is choose a single field, no criteria, no nothing:

SELECT SalesCombinedLicensee.invoice_date
FROM SalesCombinedLicensee;

SalesCombinedLicensee works perfectly fine. Why would THIS query result
in "query too cmplex?!!!!!!!!!

The query which works is as follows:


SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id
FROM territory INNER JOIN (customer INNER JOIN (((item_types INNER JOIN
(licensor INNER JOIN item ON licensor.licensor_id = item.pri_licensor_id)
ON item_types.type_id = item.item_type) INNER JOIN invoice_d ON
item.item_id = invoice_d.item_id) INNER JOIN invoice_h ON
invoice_d.invoice_id = invoice_h.invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_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) AND ((licensor.labels) Is
Not Null And (licensor.labels)>""));


UNION ALL SELECT invoice_h.invoice_date, invoice_h.status,
licensor.labels, item_types.name, territory.name,
IIf(territory!name="Foreign_1" Or territory!name="Foreign_2" Or
territory!name="Foreign_3","YES","NO") AS [Foreign],
invoice_d!ship_qty*invoice_d!price AS Ext_price, invoice_d.ship_qty,
invoice_d.item_id
FROM licensor INNER JOIN (territory INNER JOIN (customer INNER JOIN
(((item_types INNER JOIN item ON item_types.type_id = item.item_type)
INNER JOIN invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON territory.territory_id =
customer.territory_id) ON licensor.licensor_id = item.sec_licensor_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) AND ((licensor.labels) Is
Not Null And (licensor.labels)>""));
Click to expand...
Click to expand...
 
J

John W. Vinson

Even if all I do is choose a single field, no criteria, no nothing:

SELECT SalesCombinedLicensee.invoice_date
FROM SalesCombinedLicensee;

SalesCombinedLicensee works perfectly fine. Why would THIS query result
in "query too cmplex?!!!!!!!!!

The query which works is as follows:
Click to expand...

Well, you do have criteria - *in the UNION query itself*. Try explicitly
defining the query's parameters; change SalesCombinedLincensee to

PARAMETERS Forms![Sales_Reports_Form]![STARTDATE] DateTime,
Forms![Sales_Reports_Form]![ENDDATE] DateTime;
SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id
FROM territory INNER JOIN (customer INNER JOIN (((item_types INNER JOIN
(licensor INNER JOIN item ON licensor.licensor_id =
item.pri_licensor_id) ON item_types.type_id = item.item_type) INNER JOIN
invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN invoice_h ON
invoice_d.invoice_id = invoice_h.invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_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) AND
((licensor.labels) Is Not Null And (licensor.labels)>""));
UNION ALL SELECT invoice_h.invoice_date, invoice_h.status,
licensor.labels, item_types.name, territory.name,
IIf(territory!name="Foreign_1" Or territory!name="Foreign_2" Or
territory!name="Foreign_3","YES","NO") AS [Foreign],
invoice_d!ship_qty*invoice_d!price AS Ext_price, invoice_d.ship_qty,
invoice_d.item_id
FROM licensor INNER JOIN (territory INNER JOIN (customer INNER JOIN
(((item_types INNER JOIN item ON item_types.type_id = item.item_type)
INNER JOIN invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON territory.territory_id
= customer.territory_id) ON licensor.licensor_id = item.sec_licensor_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) AND ((licensor.labels) Is
Not Null And (licensor.labels)>""));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

4+0 = 2 in Union query. Data lost. 13
Exported query Runs forever, creates huge spreadsheet with no data. 2
Query works, query based on it fails. 2
Enter Parameter Value .WHY? 2
Can't run Query, can't build formula 2
engine does not recognize <name> as a valid field name or expression.(Error 3070) 3
Criteria fails changing Select query to crosstab. 1
Query asks for parameter with no connection to the query. 1

Top