If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum means empty query?
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 a 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)="Putaw ay 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)="Putaw ay 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; |
#2
|
|||
|
|||
Sum means empty query?
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)="Putaw ay 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" wrote in message ... 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 a 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)="Putaw ay 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)="Putaw ay 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; |
#3
|
|||
|
|||
Sum means empty query?
Maybe remove the 'Max' from
Max(FormatDateTime([item_warehouse_link_history]![date],2)) DESC; -- Build a little, test a little. "Ken Snell" wrote: 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)="Putaw ay 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" wrote in message ... 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 a 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)="Putaw ay 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)="Putaw ay 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; . |
#4
|
|||
|
|||
Sum means empty query?
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 Snell wrote: 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)="Putaw ay 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; |
#5
|
|||
|
|||
Sum means empty query?
KARL DEWEY wrote:
Maybe remove the 'Max' from Max(FormatDateTime([item_warehouse_link_history]![date],2)) DESC; Ken got me a solution that worked, removing the MAX(), even removing the whole date reference, did not make a difference. |
#6
|
|||
|
|||
Sum means empty query?
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" wrote in message ... 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 Snell wrote: 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)="Putaw ay 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; |
Thread Tools | |
Display Modes | |
|
|