Query too complex? No way.

[Phil Smith]

SELECT SalesCombinedLicensee.invoice_date
FROM SalesCombinedLicensee;

NOT a very complex query, is it? It is based on a union query, which
runs perfectly fine, returning 7427 records.

The above query is cut way down from a query which has been running fine
for about three years. Now I can't even grab a single field from it?

Please explain to me what is going on here.

Thanx

Phil

 

[Banana]

Just because you got an error with this query does not mean there is
something wrong with the query itself; it's possible it has to do with
the original UNION query which this query in question is based off.

Maybe if we saw the SQL for the underlying UNION query, we could provide
suggestions.

 

[John W. Vinson]

SELECT SalesCombinedLicensee.invoice_date
FROM SalesCombinedLicensee;

NOT a very complex query, is it? It is based on a union query, which
runs perfectly fine, returning 7427 records.

The above query is cut way down from a query which has been running fine
for about three years. Now I can't even grab a single field from it?

Please explain to me what is going on here.

Thanx

Phil

Probably database corruption: see
http://www.granite.ab.ca/access/corruptmdbs.htm

for symptoms, causes and cures.

 

[Phil Smith]

Two Points.
The underlying union query works just fine.
Both of these queries have been workign for years with no problems.

The underlying query is ugly, but here it is in case it helps.

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)>""));