Query too complex my ass

Phil Smith · Oct 9, 2009

I have a deveopment version and a production version of a database.

This query works fine in the produciton version, but stopped working in
my production version. I can alwasy go back to my production version,
but I would loose many hours of work if I did so.

The query that fails, (and the underlying query) has not been touched.
I am sure this is database corruption, but of course, compact and repair
is not fixing it.
Anything I can do to fix this before I take out a contract on Gates?

SELECT [Promo Recode Union complete].Acct, [Promo Recode Union
complete].Division, [Promo Recode Union complete].Dept, [Promo Recode
Union complete].Category, "" AS Warehouse, [Promo Recode Union
complete].Brand, "Promo Recode for invoices between " &
Forms!Sales_Reports_Form!STARTDATE & " and " &
Forms!Sales_Reports_Form!ENDDATE AS [Memo], Sum([Promo Recode Union
complete].ext_Cost) AS SumOfext_Cost
FROM [Promo Recode Union complete]
GROUP BY [Promo Recode Union complete].Acct, [Promo Recode Union
complete].Division, [Promo Recode Union complete].Dept, [Promo Recode
Union complete].Category, "", [Promo Recode Union complete].Brand,
"Promo Recode for invoices between " &
Forms!Sales_Reports_Form!STARTDATE & " and " &
Forms!Sales_Reports_Form!ENDDATE
ORDER BY [Promo Recode Union complete].Acct, [Promo Recode Union
complete].Division, [Promo Recode Union complete].Dept, [Promo Recode
Union complete].Category, [Promo Recode Union complete].Brand;

KARL DEWEY · Oct 9, 2009

The query that fails, (and the underlying query) has not been touched.
You did not post the underlying query. Data can cause a query to fail
particular a Null when doing math on a field.

Phil Smith · Oct 9, 2009

The query it is based on runs perfectly fine, but I will post it at the
bottom. Something else weird? It started with a change request. With
my DEV version down, I made a copy of production and added my new query.
When I put too many fields, (about a dozen,) basedon that same
underlying query, it works fine. As I REDUCE the number of fields, the
query somehow becomes too complex.

So here are three queries. The underlying query, which works fine, is a
massive six piece union query:
------------------------------------------------------------
SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 9140 AS Acct,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON
invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
division.division_id=item.division_id) ON brand.brand_id=item.brand_id)
ON category.category_id=item.master_category_id
WHERE (((term.name)="Warranty") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));
union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer id]=customer.customer_id
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="SAMPLES" Or
(customer_type.name)="PROMO") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer type]=customer_type.name
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 5400 AS Acct,
(invoice_d!cost*invoice_d!ship_qty)*-1 AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, "Corporate" AS Division, "" AS
Dept, "" AS Category, "" AS Brand, 2100 AS Acct,
(invoice_d!cost*invoice_d!ship_qty*0.095)*-1 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division,
[Promo recode map].Department, "" AS Category, [Promo recode map].Brand,
[Promo recode map].[Sales Tax Acct],
invoice_d!cost*invoice_d!ship_qty*0.095 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN ([Promo recode map] INNER JOIN (brand INNER
JOIN (division INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON [Promo recode map].[Customer id] =
customer.customer_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

UNION ALL SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Sales Tax Acct] AS
Expr4, (invoice_d!cost*invoice_d!ship_qty)*0.095 AS ext_Cost,
invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON [Promo recode map].[Customer type] = customer_type.name
WHERE (((term.name)<>"Warranty") AND ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));
---------------------------------------------------------------------------
The query based on that query, which also runs fine.
------------------------------------------------------------------------
SELECT [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name, territory.name
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name,
territory.name;
-------------------------------------------------------------------------
The query which fails, which is the exact same as above except for
having LESS fields:
--------------------------------------------------------------------------
SELECT territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost;

S.Clark · Oct 9, 2009

The queries, individually, can run without issue. But, when you chain them,
it's not uncommon for Access to choke. For example... on a 6 section UNION
query.

Worst case, use an action query (or three) to put the data into a temp
table, then use that as the basis for the aggregations.

Phil Smith said:
The query it is based on runs perfectly fine, but I will post it at the
bottom. Something else weird? It started with a change request. With
my DEV version down, I made a copy of production and added my new query.
When I put too many fields, (about a dozen,) basedon that same
underlying query, it works fine. As I REDUCE the number of fields, the
query somehow becomes too complex.

So here are three queries. The underlying query, which works fine, is a
massive six piece union query:
------------------------------------------------------------
SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 9140 AS Acct,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON
invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
division.division_id=item.division_id) ON brand.brand_id=item.brand_id)
ON category.category_id=item.master_category_id
WHERE (((term.name)="Warranty") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));
union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer id]=customer.customer_id
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="SAMPLES" Or
(customer_type.name)="PROMO") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer type]=customer_type.name
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 5400 AS Acct,
(invoice_d!cost*invoice_d!ship_qty)*-1 AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, "Corporate" AS Division, "" AS
Dept, "" AS Category, "" AS Brand, 2100 AS Acct,
(invoice_d!cost*invoice_d!ship_qty*0.095)*-1 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division,
[Promo recode map].Department, "" AS Category, [Promo recode map].Brand,
[Promo recode map].[Sales Tax Acct],
invoice_d!cost*invoice_d!ship_qty*0.095 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN ([Promo recode map] INNER JOIN (brand INNER
JOIN (division INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON [Promo recode map].[Customer id] =
customer.customer_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

UNION ALL SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Sales Tax Acct] AS
Expr4, (invoice_d!cost*invoice_d!ship_qty)*0.095 AS ext_Cost,
invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON [Promo recode map].[Customer type] = customer_type.name
WHERE (((term.name)<>"Warranty") AND ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));
---------------------------------------------------------------------------
The query based on that query, which also runs fine.
------------------------------------------------------------------------
SELECT [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name, territory.name
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name,
territory.name;
-------------------------------------------------------------------------
The query which fails, which is the exact same as above except for
having LESS fields:
--------------------------------------------------------------------------
SELECT territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost;
---------------------------------------------------------------------------

KARL said:

You did not post the underlying query. Data can cause a query to fail
particular a Null when doing math on a field.

Click to expand...

KARL DEWEY · Oct 9, 2009

I do not follow what you are doing due to the size of it all but one question
comes to mind -- Why are you grouping on [Promo Recode Union
complete].ext_Cost?
I would have though you would Sum that field.

--
Build a little, test a little.

Phil Smith said:
The query it is based on runs perfectly fine, but I will post it at the
bottom. Something else weird? It started with a change request. With
my DEV version down, I made a copy of production and added my new query.
When I put too many fields, (about a dozen,) basedon that same
underlying query, it works fine. As I REDUCE the number of fields, the
query somehow becomes too complex.

So here are three queries. The underlying query, which works fine, is a
massive six piece union query:
------------------------------------------------------------
SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 9140 AS Acct,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON
invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
division.division_id=item.division_id) ON brand.brand_id=item.brand_id)
ON category.category_id=item.master_category_id
WHERE (((term.name)="Warranty") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));
union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer id]=customer.customer_id
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="SAMPLES" Or
(customer_type.name)="PROMO") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer type]=customer_type.name
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 5400 AS Acct,
(invoice_d!cost*invoice_d!ship_qty)*-1 AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, "Corporate" AS Division, "" AS
Dept, "" AS Category, "" AS Brand, 2100 AS Acct,
(invoice_d!cost*invoice_d!ship_qty*0.095)*-1 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division,
[Promo recode map].Department, "" AS Category, [Promo recode map].Brand,
[Promo recode map].[Sales Tax Acct],
invoice_d!cost*invoice_d!ship_qty*0.095 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN ([Promo recode map] INNER JOIN (brand INNER
JOIN (division INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON [Promo recode map].[Customer id] =
customer.customer_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

UNION ALL SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Sales Tax Acct] AS
Expr4, (invoice_d!cost*invoice_d!ship_qty)*0.095 AS ext_Cost,
invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON [Promo recode map].[Customer type] = customer_type.name
WHERE (((term.name)<>"Warranty") AND ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));
---------------------------------------------------------------------------
The query based on that query, which also runs fine.
------------------------------------------------------------------------
SELECT [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name, territory.name
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name,
territory.name;
-------------------------------------------------------------------------
The query which fails, which is the exact same as above except for
having LESS fields:
--------------------------------------------------------------------------
SELECT territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost;
---------------------------------------------------------------------------

KARL said:

You did not post the underlying query. Data can cause a query to fail
particular a Null when doing math on a field.

Click to expand...

Phil Smith · Oct 9, 2009

You mean in the final query that is "Too Complex?"

Actually, My final intention is to have just Territory.name and
....Ext_Cost summed as you suggest. I simply began deleting coloums
until it failed with query too complex. I figured it would be easier to
identify the problem if it was as close to the one that worked as
possible. Putting a sum in there might ahve changed something. The
bottom line question for is is why doe removing fields from the SELECT,
when there is no criteria to worry about, make the query "too complex?"
By any stretch of Microsoft's vivid imagination, pulling feilds out
makes it LESS complex...

IF I do change that field to sum up, It still fails with "Too Complex..."

Phil Smith · Oct 9, 2009

Although I am fine with that solution, if need be, two points come to mind:
1) Shouldn't have to, cuz Access should be able to handle it. If it
can't, then a real reason as opposed to this bogus "too complex" would
be nice.
2) IT USED TO WORK FINE, without any changes whatsoever on the original
query. AND it still works in my production version. If it is
corruption, why can't I compact and repair it away? If it is not, then
what is it?

So while I don't mind rewriting this process to use a temp table if it
will work, I am not convinced that it will work, and then continue to
work. Is access 2007 sp3 really that screwed up and unstable? Becasue
I do get this type of weirdness fomr time to time, and it is really
getting old. It is annoying enough that I have to wait five minutes for
my screen to settle down scolling though my list of queries before I can
actually choose one of them.

S.Clark said:
The queries, individually, can run without issue. But, when you chain them,
it's not uncommon for Access to choke. For example... on a 6 section UNION
query.

Worst case, use an action query (or three) to put the data into a temp
table, then use that as the basis for the aggregations.

:

The query it is based on runs perfectly fine, but I will post it at the
bottom. Something else weird? It started with a change request. With
my DEV version down, I made a copy of production and added my new query.
When I put too many fields, (about a dozen,) basedon that same
underlying query, it works fine. As I REDUCE the number of fields, the
query somehow becomes too complex.

So here are three queries. The underlying query, which works fine, is a
massive six piece union query:
------------------------------------------------------------
SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 9140 AS Acct,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON
invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
division.division_id=item.division_id) ON brand.brand_id=item.brand_id)
ON category.category_id=item.master_category_id
WHERE (((term.name)="Warranty") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));
union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer id]=customer.customer_id
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="SAMPLES" Or
(customer_type.name)="PROMO") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer type]=customer_type.name
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 5400 AS Acct,
(invoice_d!cost*invoice_d!ship_qty)*-1 AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, "Corporate" AS Division, "" AS
Dept, "" AS Category, "" AS Brand, 2100 AS Acct,
(invoice_d!cost*invoice_d!ship_qty*0.095)*-1 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division,
[Promo recode map].Department, "" AS Category, [Promo recode map].Brand,
[Promo recode map].[Sales Tax Acct],
invoice_d!cost*invoice_d!ship_qty*0.095 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN ([Promo recode map] INNER JOIN (brand INNER
JOIN (division INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON [Promo recode map].[Customer id] =
customer.customer_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

UNION ALL SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Sales Tax Acct] AS
Expr4, (invoice_d!cost*invoice_d!ship_qty)*0.095 AS ext_Cost,
invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON [Promo recode map].[Customer type] = customer_type.name
WHERE (((term.name)<>"Warranty") AND ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));
---------------------------------------------------------------------------
The query based on that query, which also runs fine.
------------------------------------------------------------------------
SELECT [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name, territory.name
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name,
territory.name;
-------------------------------------------------------------------------
The query which fails, which is the exact same as above except for
having LESS fields:
--------------------------------------------------------------------------
SELECT territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost;
---------------------------------------------------------------------------

KARL said:

The query that fails, (and the underlying query) has not been touched.

You did not post the underlying query. Data can cause a query to fail
particular a Null when doing math on a field.

Click to expand...

Click to expand...

Banana · Oct 9, 2009

Phil,

It's not just selecting less fields but also grouping by less fields,
which can be very very different thing. As Karl suggested, if there is a
calculation that doesn't make sense, it'd choke the query.

It may only have had worked by accident but now is blowing up in your
face because you changed the grouping and thus introduced an entirely
different calculation that now makes no sense.

To be quite honest, I don't even understand why you need 6-part union
query; looking it over, it seems to me it should be possible to
accomplish the same thing using single query joining the tables in
correct manner, or maybe at least two-part union between category table
and Promo Recode Map table with the WHERE criteria appropriately combined.

I would encourage you to let go of the preconception that just because
it worked, it means it was fine all along. I can fix up a leaky radiator
by applying lot of duct tapes but that doesn't really fix it. Same idea
here.

It may do us more good to take a fresh look at the query and figure out
what you are trying to achieve and see if we can discover a simpler
query that won't be so troublesome.

Phil said:
The query it is based on runs perfectly fine, but I will post it at the
bottom. Something else weird? It started with a change request. With
my DEV version down, I made a copy of production and added my new query.
When I put too many fields, (about a dozen,) basedon that same
underlying query, it works fine. As I REDUCE the number of fields, the
query somehow becomes too complex.

So here are three queries. The underlying query, which works fine, is a
massive six piece union query:
------------------------------------------------------------
SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 9140 AS Acct,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON
invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
division.division_id=item.division_id) ON brand.brand_id=item.brand_id)
ON category.category_id=item.master_category_id
WHERE (((term.name)="Warranty") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));
union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer id]=customer.customer_id
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="SAMPLES" Or
(customer_type.name)="PROMO") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer type]=customer_type.name
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 5400 AS Acct,
(invoice_d!cost*invoice_d!ship_qty)*-1 AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, "Corporate" AS Division, "" AS
Dept, "" AS Category, "" AS Brand, 2100 AS Acct,
(invoice_d!cost*invoice_d!ship_qty*0.095)*-1 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division,
[Promo recode map].Department, "" AS Category, [Promo recode map].Brand,
[Promo recode map].[Sales Tax Acct],
invoice_d!cost*invoice_d!ship_qty*0.095 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN ([Promo recode map] INNER JOIN (brand INNER
JOIN (division INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON [Promo recode map].[Customer id] =
customer.customer_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));

UNION ALL SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Sales Tax Acct] AS
Expr4, (invoice_d!cost*invoice_d!ship_qty)*0.095 AS ext_Cost,
invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON [Promo recode map].[Customer type] = customer_type.name
WHERE (((term.name)<>"Warranty") AND ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or (customer_type.name)="KEY
DISTRIBUTOR" Or (customer_type.name)="KEY WHOLESALE" Or
(customer_type.name)="HOUSE ACCOUNTS" Or (customer_type.name)="FOREIGN
WHOLESALE") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_d.price)=0));
---------------------------------------------------------------------------
The query based on that query, which also runs fine.
------------------------------------------------------------------------
SELECT [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name,
territory.name
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union complete].Acct,
[Promo Recode Union complete].ext_Cost, [Promo Recode Union
complete].invoice_date, invoice_h.customer_id, customer.name,
territory.name;
-------------------------------------------------------------------------
The query which fails, which is the exact same as above except for
having LESS fields:
--------------------------------------------------------------------------
SELECT territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost;
---------------------------------------------------------------------------

KARL said:

You did not post the underlying query. Data can cause a query to
fail particular a Null when doing math on a field.

Click to expand...

Tony Toews [MVP] · Oct 9, 2009

Phil Smith said:
The query that fails, (and the underlying query) has not been touched.
I am sure this is database corruption, but of course, compact and repair
is not fixing it.

Compact & Report only detects and repairs corruptions in tables and
indexes.

To location corruptions in other objects, queries, forms, reports,
macros or VBA modules you need to do an import.

Tony

John W. Vinson · Oct 9, 2009

I have a deveopment version and a production version of a database.

This query works fine in the produciton version, but stopped working in
my production version. I can alwasy go back to my production version,
but I would loose many hours of work if I did so.

The query that fails, (and the underlying query) has not been touched.
I am sure this is database corruption, but of course, compact and repair
is not fixing it.
Anything I can do to fix this before I take out a contract on Gates?

SELECT [Promo Recode Union complete].Acct, [Promo Recode Union
complete].Division, [Promo Recode Union complete].Dept, [Promo Recode
Union complete].Category, "" AS Warehouse, [Promo Recode Union
complete].Brand, "Promo Recode for invoices between " &
Forms!Sales_Reports_Form!STARTDATE & " and " &
Forms!Sales_Reports_Form!ENDDATE AS [Memo], Sum([Promo Recode Union
complete].ext_Cost) AS SumOfext_Cost
FROM [Promo Recode Union complete]
GROUP BY [Promo Recode Union complete].Acct, [Promo Recode Union
complete].Division, [Promo Recode Union complete].Dept, [Promo Recode
Union complete].Category, "", [Promo Recode Union complete].Brand,
"Promo Recode for invoices between " &
Forms!Sales_Reports_Form!STARTDATE & " and " &
Forms!Sales_Reports_Form!ENDDATE
ORDER BY [Promo Recode Union complete].Acct, [Promo Recode Union
complete].Division, [Promo Recode Union complete].Dept, [Promo Recode
Union complete].Category, [Promo Recode Union complete].Brand;

I've often seen bogus "Query Too Complex" messages disappear when you
explicitly define the Parameters of the query:

PARAMETERS Forms!Sales_Reports_Form!STARTDATE DateTime,
Forms!Sales_Reports_Form!ENDDATE DateTime;
SELECT ...

Phil Smith · Oct 9, 2009

Thanx in reading and responding.

I would think less grouping would also mean less complex, but Like I
said, nothing changed, not the grouping,not the selecting, not even the
data except to add more records.

In terms of my "preconception", I get your point but let me be clear:
the exact same code, working on the exact same externally linked data
with the same criteria, works in one database, but does not work in
another. Seperate from that, addtional code is faling that should be OK.

I wouldn't expect you to able to see why I need 7(!) because it is a
real bear of a job, but if you really think this can be simplified and
want to take a stab at helping in that regard, great. It is greatly
appreciated. If you turn and run screaming after reading this, I will
uinderstand.

The purpose of all this is to generate, along with some supporting
reports, a monster journal entry to recode the costs associated with
products given away for free to the proper departments.
For each record, we need four seperate entries: Two to move sales tax
data between accounts, and two to move CostOfGoods between accounts.
The TO side for cost of goods varies depending on if they are warranty
replacements,(all to one account) certain customer types, (To varies by
customer, each customer represent marketing giveaways per brand), and
the rest which vary by customer type.

1st Union: Any orders with terms of "warranty," regardless of customer,
recodes to our warranty account. AS a Debit (Positive#)

This is where the Recode map comes in.
2nd Union: For Certain Customer types, the account to recode to is
Customer specific. So for SAMPLE and PROMO customers, I must join on
CUSTOMER_ID and pull the accounts from the Map. AS a Debit (Positive#)

3rd Union: For other Customer types, the map is the same for all customr
types, so for them, I join on CUSTOMER_TYPE, and pull the accounts from
Map. AS a Debit (Positive#)

Those three create one side of the COGS JL, where the money is going TO.
Then we need to designate where the money is coming FROM.
That is the 4th Union. It all comes from the Sales Account, regardless
of where it was sent by the first three unions. As a Credit (Negative #)

The 5th Union recodes the sales tax, (which we have to pay since we are
not charging anyone) from sales tax expense. AS a Debit (Positive#)

The 6th and 7th Union Recodes the sales tax to the correct sales tax
payable accounts. As a Credit (Negative #)

Some entries in the map will have a customer ID but no Customer type,
the rest will have a customer type but no customer ID.

Once all of that is pulled out, I sort and sum based on each account,
division, brand, etc. to come up with a GL entry that is typically about
300 lines, which we import into our accounting system.

The only change I can conceive of is to have each seperate query append
the results to a common table, and base the rest on that, although I
lose the distinct selection property inherit in a union query. This
should not be a problem anyway.

Phil said:
Phil,

It's not just selecting less fields but also grouping by less fields,
which can be very very different thing. As Karl suggested, if there is a
calculation that doesn't make sense, it'd choke the query.

It may only have had worked by accident but now is blowing up in your
face because you changed the grouping and thus introduced an entirely
different calculation that now makes no sense.

To be quite honest, I don't even understand why you need 6-part union
query; looking it over, it seems to me it should be possible to
accomplish the same thing using single query joining the tables in
correct manner, or maybe at least two-part union between category table
and Promo Recode Map table with the WHERE criteria appropriately combined.

I would encourage you to let go of the preconception that just because
it worked, it means it was fine all along. I can fix up a leaky radiator
by applying lot of duct tapes but that doesn't really fix it. Same idea
here.

It may do us more good to take a fresh look at the query and figure out
what you are trying to achieve and see if we can discover a simpler
query that won't be so troublesome.

Phil said:

The query it is based on runs perfectly fine, but I will post it at
the bottom. Something else weird? It started with a change request.
With my DEV version down, I made a copy of production and added my new
query. When I put too many fields, (about a dozen,) basedon that same
underlying query, it works fine. As I REDUCE the number of fields,
the query somehow becomes too complex.

So here are three queries. The underlying query, which works fine, is
a massive six piece union query:
------------------------------------------------------------
SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 9140 AS Acct,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON
invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
division.division_id=item.division_id) ON
brand.brand_id=item.brand_id) ON
category.category_id=item.master_category_id
WHERE (((term.name)="Warranty") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));
union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer id]=customer.customer_id
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="SAMPLES"
Or (customer_type.name)="PROMO") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Promo Acct] AS Expr4,
invoice_d!cost*invoice_d!ship_qty AS ext_Cost, invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id=invoice_h.invoice_id) ON
customer.customer_id=invoice_h.customer_id) ON
customer_type.customer_type_id=customer.customer_type_id) ON
item.item_id=invoice_d.item_id) ON term.term_id=invoice_h.term_id) ON
[Promo recode map].[Customer type]=customer_type.name
WHERE (((term.name)<>"Warranty") And ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or
(customer_type.name)="KEY DISTRIBUTOR" Or (customer_type.name)="KEY
WHOLESALE" Or (customer_type.name)="HOUSE ACCOUNTS" Or
(customer_type.name)="FOREIGN WHOLESALE") And
((invoice_h.invoice_date)>=Forms!Sales_Reports_Form!STARTDATE And
(invoice_h.invoice_date)<=Forms!Sales_Reports_Form!ENDDATE) And
((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, division.name AS Division, "" AS
Dept, category.name AS Category, brand.name AS Brand, 5400 AS Acct,
(invoice_d!cost*invoice_d!ship_qty)*-1 AS ext_Cost,
invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE]
And (invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE])
AND ((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, "Corporate" AS Division, "" AS
Dept, "" AS Category, "" AS Brand, 2100 AS Acct,
(invoice_d!cost*invoice_d!ship_qty*0.095)*-1 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (term
INNER JOIN (item INNER JOIN (customer_type INNER JOIN (customer INNER
JOIN (invoice_d INNER JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE]
And (invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE])
AND ((invoice_d.price)=0));

union all

SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division,
[Promo recode map].Department, "" AS Category, [Promo recode
map].Brand, [Promo recode map].[Sales Tax Acct],
invoice_d!cost*invoice_d!ship_qty*0.095 AS extSalesTax,
invoice_h.invoice_date
FROM category INNER JOIN ([Promo recode map] INNER JOIN (brand INNER
JOIN (division INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON division.division_id = item.division_id) ON brand.brand_id =
item.brand_id) ON [Promo recode map].[Customer id] =
customer.customer_id) ON category.category_id = item.master_category_id
WHERE (((term.name)<>"Warranty") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE]
And (invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE])
AND ((invoice_d.price)=0));

UNION ALL SELECT invoice_d.invoice_id, invoice_d.line_id, term.name,
customer_type.name AS CustomerType, customer.name, invoice_d.item_id,
invoice_d.order_qty, item.short_desc, [Promo recode map].Division AS
Expr1, [Promo recode map].Department AS Expr2, "" AS Category, [Promo
recode map].Brand AS Expr3, [Promo recode map].[Sales Tax Acct] AS
Expr4, (invoice_d!cost*invoice_d!ship_qty)*0.095 AS ext_Cost,
invoice_h.invoice_date
FROM [Promo recode map] INNER JOIN (term INNER JOIN (item INNER JOIN
(customer_type INNER JOIN (customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
item.item_id = invoice_d.item_id) ON term.term_id = invoice_h.term_id)
ON [Promo recode map].[Customer type] = customer_type.name
WHERE (((term.name)<>"Warranty") AND ((customer_type.name)="CORE
WHOLESALE" Or (customer_type.name)="EMPLOYEE PURCHASES" Or
(customer_type.name)="FOREIGN DISTRIBUTOR" Or
(customer_type.name)="KEY DISTRIBUTOR" Or (customer_type.name)="KEY
WHOLESALE" Or (customer_type.name)="HOUSE ACCOUNTS" Or
(customer_type.name)="FOREIGN WHOLESALE") AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE]
And (invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE])
AND ((invoice_d.price)=0));
---------------------------------------------------------------------------

The query based on that query, which also runs fine.
------------------------------------------------------------------------
SELECT [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union
complete].Acct, [Promo Recode Union complete].ext_Cost, [Promo Recode
Union complete].invoice_date, invoice_h.customer_id, customer.name,
territory.name
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY [Promo Recode Union complete].invoice_id, [Promo Recode Union
complete].line_id, [Promo Recode Union complete].term.name, [Promo
Recode Union complete].CustomerType, [Promo Recode Union
complete].customer.name, [Promo Recode Union complete].item_id, [Promo
Recode Union complete].order_qty, [Promo Recode Union
complete].short_desc, [Promo Recode Union complete].Division, [Promo
Recode Union complete].Dept, [Promo Recode Union complete].Category,
[Promo Recode Union complete].Brand, [Promo Recode Union
complete].Acct, [Promo Recode Union complete].ext_Cost, [Promo Recode
Union complete].invoice_date, invoice_h.customer_id, customer.name,
territory.name;
-------------------------------------------------------------------------
The query which fails, which is the exact same as above except for
having LESS fields:
--------------------------------------------------------------------------

SELECT territory.name, [Promo Recode Union complete].Division, [Promo
Recode Union complete].ext_Cost
FROM territory INNER JOIN (customer INNER JOIN (invoice_h INNER JOIN
[Promo Recode Union complete] ON invoice_h.invoice_id = [Promo Recode
Union complete].invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
GROUP BY territory.name, [Promo Recode Union complete].Division,
[Promo Recode Union complete].ext_Cost;
---------------------------------------------------------------------------

KARL said:

The query that fails, (and the underlying query) has not been touched.

You did not post the underlying query. Data can cause a query to
fail particular a Null when doing math on a field.

Click to expand...

Click to expand...

Jeff Boyce · Oct 10, 2009

Like John V., I've had Access 2007 throw out query error messages that
didn't make sense. These disappeared after explicitly declaring the
parameters.

Perhaps the latest version of Access is less forgiving about parameterized
queries in which the parameters have not been explicitly declared...

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

Phil Smith · Oct 10, 2009

two questions.:
1) Where Do I put my parameters? In the query that is failing, in the
underlying union query, or both?

2) If I put Paramters in the Union Query, do I put them into each of my
unions? The only criteria pieces that would need it are Start and End
Dates on my form, which is the same over each piece of the union query.
Just the first piece of the query?

I have a deveopment version and a production version of a database.

This query works fine in the produciton version, but stopped working in
my production version. I can alwasy go back to my production version,
but I would loose many hours of work if I did so.

The query that fails, (and the underlying query) has not been touched.
I am sure this is database corruption, but of course, compact and repair
is not fixing it.
Anything I can do to fix this before I take out a contract on Gates?

SELECT [Promo Recode Union complete].Acct, [Promo Recode Union
complete].Division, [Promo Recode Union complete].Dept, [Promo Recode
Union complete].Category, "" AS Warehouse, [Promo Recode Union
complete].Brand, "Promo Recode for invoices between " &
Forms!Sales_Reports_Form!STARTDATE & " and " &
Forms!Sales_Reports_Form!ENDDATE AS [Memo], Sum([Promo Recode Union
complete].ext_Cost) AS SumOfext_Cost

Click to expand...

FROM [Promo Recode Union complete]

Click to expand...

GROUP BY [Promo Recode Union complete].Acct, [Promo Recode Union
complete].Division, [Promo Recode Union complete].Dept, [Promo Recode
Union complete].Category, "", [Promo Recode Union complete].Brand,
"Promo Recode for invoices between " &
Forms!Sales_Reports_Form!STARTDATE & " and " &
Forms!Sales_Reports_Form!ENDDATE
ORDER BY [Promo Recode Union complete].Acct, [Promo Recode Union
complete].Division, [Promo Recode Union complete].Dept, [Promo Recode
Union complete].Category, [Promo Recode Union complete].Brand;

Click to expand...

I've often seen bogus "Query Too Complex" messages disappear when you
explicitly define the Parameters of the query:

PARAMETERS Forms!Sales_Reports_Form!STARTDATE DateTime,
Forms!Sales_Reports_Form!ENDDATE DateTime;
SELECT ...

John W. Vinson · Oct 10, 2009

two questions.:
1) Where Do I put my parameters? In the query that is failing, in the
underlying union query, or both?

Before the SELECT statement of the query invoking the parameters. I *think*
you can put it only before the first SELECT but I'm not certain.

2) If I put Paramters in the Union Query, do I put them into each of my
unions? The only criteria pieces that would need it are Start and End
Dates on my form, which is the same over each piece of the union query.
Just the first piece of the query?

All I can say is... try it and see.

Query too complex my ass

Phil Smith

KARL DEWEY

Phil Smith

S.Clark

KARL DEWEY

Phil Smith

Phil Smith

Banana

Tony Toews [MVP]

John W. Vinson

Phil Smith

Jeff Boyce

Phil Smith

John W. Vinson