Another query too complex problem

  • Thread starter Thread starter Phil Smith
  • Start date Start date
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)>""));
 
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...
 
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...
 
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)>""));
 
Back
Top