Exported query Runs forever, creates huge spreadsheet with no data.

[Phil Smith]

I do not understand it. This query takes a full day to run, So I
export the query to excel. When it is done I get a spreadhseet that is
2.5 megabytes, but has no data.

TRANSFORM
Sum(IIf(ItemWorkingWindowMultiplier!Valuetouse="Units",invoice_d!ship_qty,0)+IIf(ItemWorkingWindowMultiplier!Valuetouse="Cost",invoice_d!cost*invoice_d!ship_qty,0)+IIf(ItemWorkingWindowMultiplier!Valuetouse="Sales",invoice_d!ship_qty*invoice_d!price,0)+IIf(ItemWorkingWindowMultiplier!Valuetouse="Margin",(invoice_d!price-invoice_d!cost)*invoice_d!ship_qty,0))
AS [Value]
SELECT invoice_d.item_id, First(item.short_desc) AS FirstOfshort_desc,
Sum(invoice_d.ship_qty) AS SumOfship_qty,
First(ItemFirstSales.MinOfinvoice_date) AS FirstOfMinOfinvoice_date,
Max(invoice_h.invoice_date) AS MaxOfinvoice_date
FROM ItemWorkingWindowMultiplier, customer_type INNER JOIN
(ItemFirstSales INNER JOIN (item INNER JOIN (customer INNER JOIN
(invoice_h INNER JOIN invoice_d ON invoice_h.invoice_id =
invoice_d.invoice_id) ON customer.customer_id = invoice_h.customer_id)
ON item.item_id = invoice_d.item_id) ON ItemFirstSales.item_id =
invoice_d.item_id) ON customer_type.customer_type_id =
customer.customer_type_id
WHERE (((invoice_d.item_id)>100) AND ((invoice_h.status)=8 Or
(invoice_h.status)=9) AND ((invoice_d.item_id)>0) AND
((invoice_d.ship_qty)>0) AND ((customer_type.name)<>"ProForm" And
(customer_type.name)<>"EMPLOYEE PURCHASES" And
(customer_type.name)<>"WARRANTY" And (customer_type.name)<>"SAMPLES" And
(customer_type.name)<>"PROMO" And (customer_type.name)<>"Unassigned"))
GROUP BY invoice_d.item_id, customer_type.name, invoice_h.invoice_date
ORDER BY invoice_d.item_id
PIVOT "Month " &
IIf((DateDiff("m",[MinOfinvoice_date],[invoice_date])+1)=1," ","") &
(DateDiff("m",[MinOfinvoice_date],[invoice_date])+1) &
ItemWorkingWindowMultiplier!Valuetouse;

 

[Phil Smith]

I may have figured it out. What happens if you try to export a query
which results in more then 65536 rows?

 

[John W. Vinson]

What happens if you try to export a query
which results in more then 65536 rows?

Unless you're exporting to Excel2007 (and use the appropriate Excel version in
Files Of Type) you'll get an error or a crash. Excel through 2003 only
supported 65536 rows.