P
Phil Smith
I have a query, five table join, but nothing really complex.
I run it, I get 339 records.
I hit the Totals button, add a couple Group By, a Max and SUM, and run
it. I get NOTHING. ZERO records returned. I am doing this in the
design grid. No changes in the criteria, but no data comes out. What
Gives?
They are:
Works:
SELECT item_types.name AS Type, brand.name AS Brand, item.old_sku,
item.long_desc, item_warehouse_link_history.change_qty AS [QTY Put
Away], item_warehouse_link.onhand_qty,
FormatDateTime([item_warehouse_link_history]![date],2) AS Expr1
FROM brand INNER JOIN (item_warehouse_link INNER JOIN (item_types INNER
JOIN (item_warehouse_link_history INNER JOIN item ON
item_warehouse_link_history.item_id = item.item_id) ON
item_types.type_id = item.item_type) ON item_warehouse_link.item_id =
item.item_id) ON brand.brand_id = item.brand_id
WHERE (((item_types.name)<>"Raw Apparel Misc" And
(item_types.name)<>"Raw Skate Misc") AND
((item_warehouse_link_history.change_qty)>0) AND
((item_warehouse_link_history.warehouse_id)=1) AND
((item_warehouse_link_history.post_process)="Putaway Product") AND
((item_warehouse_link_history.date)>=DateAdd("d",-14,Now())) AND
((item_warehouse_link.warehouse_id)=1))
ORDER BY item_types.name, brand.name, item.old_sku, item.long_desc,
FormatDateTime([item_warehouse_link_history]![date],2) DESC;
Fails:
SELECT item_types.name AS Type, brand.name AS Brand, item.old_sku,
item.long_desc, Sum(item_warehouse_link_history.change_qty) AS [QTY Put
Away], Sum(item_warehouse_link.onhand_qty) AS SumOfonhand_qty,
Max(FormatDateTime([item_warehouse_link_history]![date],2)) AS Expr1
FROM brand INNER JOIN (item_warehouse_link INNER JOIN (item_types INNER
JOIN (item_warehouse_link_history INNER JOIN item ON
item_warehouse_link_history.item_id = item.item_id) ON
item_types.type_id = item.item_type) ON item_warehouse_link.item_id =
item.item_id) ON brand.brand_id = item.brand_id
WHERE (((item_warehouse_link_history.warehouse_id)=1) AND
((item_warehouse_link_history.post_process)="Putaway Product") AND
((item_warehouse_link_history.date)>=DateAdd("d",-14,Now())) AND
((item_warehouse_link.warehouse_id)=1))
GROUP BY item_types.name, brand.name, item.old_sku, item.long_desc
HAVING (((item_types.name)<>"Raw Apparel Misc" And
(item_types.name)<>"Raw Skate Misc") AND
((Sum(item_warehouse_link_history.change_qty))>0))
ORDER BY item_types.name, brand.name, item.old_sku, item.long_desc,
Max(FormatDateTime([item_warehouse_link_history]![date],2)) DESC;
I run it, I get 339 records.
I hit the Totals button, add a couple Group By, a Max and SUM, and run
it. I get NOTHING. ZERO records returned. I am doing this in the
design grid. No changes in the criteria, but no data comes out. What
Gives?
They are:
Works:
SELECT item_types.name AS Type, brand.name AS Brand, item.old_sku,
item.long_desc, item_warehouse_link_history.change_qty AS [QTY Put
Away], item_warehouse_link.onhand_qty,
FormatDateTime([item_warehouse_link_history]![date],2) AS Expr1
FROM brand INNER JOIN (item_warehouse_link INNER JOIN (item_types INNER
JOIN (item_warehouse_link_history INNER JOIN item ON
item_warehouse_link_history.item_id = item.item_id) ON
item_types.type_id = item.item_type) ON item_warehouse_link.item_id =
item.item_id) ON brand.brand_id = item.brand_id
WHERE (((item_types.name)<>"Raw Apparel Misc" And
(item_types.name)<>"Raw Skate Misc") AND
((item_warehouse_link_history.change_qty)>0) AND
((item_warehouse_link_history.warehouse_id)=1) AND
((item_warehouse_link_history.post_process)="Putaway Product") AND
((item_warehouse_link_history.date)>=DateAdd("d",-14,Now())) AND
((item_warehouse_link.warehouse_id)=1))
ORDER BY item_types.name, brand.name, item.old_sku, item.long_desc,
FormatDateTime([item_warehouse_link_history]![date],2) DESC;
Fails:
SELECT item_types.name AS Type, brand.name AS Brand, item.old_sku,
item.long_desc, Sum(item_warehouse_link_history.change_qty) AS [QTY Put
Away], Sum(item_warehouse_link.onhand_qty) AS SumOfonhand_qty,
Max(FormatDateTime([item_warehouse_link_history]![date],2)) AS Expr1
FROM brand INNER JOIN (item_warehouse_link INNER JOIN (item_types INNER
JOIN (item_warehouse_link_history INNER JOIN item ON
item_warehouse_link_history.item_id = item.item_id) ON
item_types.type_id = item.item_type) ON item_warehouse_link.item_id =
item.item_id) ON brand.brand_id = item.brand_id
WHERE (((item_warehouse_link_history.warehouse_id)=1) AND
((item_warehouse_link_history.post_process)="Putaway Product") AND
((item_warehouse_link_history.date)>=DateAdd("d",-14,Now())) AND
((item_warehouse_link.warehouse_id)=1))
GROUP BY item_types.name, brand.name, item.old_sku, item.long_desc
HAVING (((item_types.name)<>"Raw Apparel Misc" And
(item_types.name)<>"Raw Skate Misc") AND
((Sum(item_warehouse_link_history.change_qty))>0))
ORDER BY item_types.name, brand.name, item.old_sku, item.long_desc,
Max(FormatDateTime([item_warehouse_link_history]![date],2)) DESC;