Sum means empty query?

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;
 
K

Ken Snell

Try this:

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_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))
GROUP BY item_types.name, brand.name, item.old_sku, item.long_desc
HAVING (((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;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Phil Smith said:
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;
Click to expand...
 
K

KARL DEWEY

Maybe remove the 'Max' from
Max(FormatDateTime([item_warehouse_link_history]![date],2)) DESC;

--
Build a little, test a little.


Ken Snell said:
Try this:

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_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))
GROUP BY item_types.name, brand.name, item.old_sku, item.long_desc
HAVING (((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;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Phil Smith said:
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;
Click to expand...


.
Click to expand...
 
P

Phil Smith

Interesting. Before I read your response, I determined that the problem
focused on [Put Away Qty]. If I removed my ">0" criteria, I got lots of
records, of course all zero.

However, after I fixed a bracketing issue, your solution seemed to work
perfectly. This is great for my project, but not much for my headache.
Can you explain what is/was going on?
 
P

Phil Smith

KARL said:
Maybe remove the 'Max' from
Max(FormatDateTime([item_warehouse_link_history]![date],2)) DESC;
Click to expand...
Ken got me a solution that worked, removing the MAX(), even removing the
whole date reference, did not make a difference.
 
K

Ken Snell

What I did was move some of your criteria from the HAVING clause to the
WHERE clause, where the criteria that I moved were the ones that you
originally had in the WHERE clause in the SELECT query.

For totals queries, HAVING clause should only filter on aggregate function
values. Use the WHERE clause for other criteria -- the query will be faster
because you're filtering the data before you aggregate the data.

As for my suggestion fixing your problem, I actually believe the >0
criterion you had on the SUM function was preventing records, as you
discovered. But I didn't say anything about it because I assumed that it was
a correct criterion, based on what you'd posted.

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Phil Smith said:
Interesting. Before I read your response, I determined that the problem
focused on [Put Away Qty]. If I removed my ">0" criteria, I got lots of
records, of course all zero.

However, after I fixed a bracketing issue, your solution seemed to work
perfectly. This is great for my project, but not much for my headache.
Can you explain what is/was going on?


Ken said:
Try this:

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_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))
GROUP BY item_types.name, brand.name, item.old_sku, item.long_desc
HAVING (((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;
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Enter Parameter Value .WHY? 2
Query asks for parameter with no connection to the query. 1
Query works, query based on it fails. 2

Top