A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sum means empty query?



 
 
Thread Tools Display Modes
  #1  
Old November 24th, 2009, 11:02 PM posted to microsoft.public.access.queries
Phil Smith
external usenet poster
 
Posts: 254
Default 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  
Old November 24th, 2009, 11:42 PM posted to microsoft.public.access.queries
Ken Snell
external usenet poster
 
Posts: 177
Default 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  
Old November 25th, 2009, 01:00 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old November 25th, 2009, 01:22 AM posted to microsoft.public.access.queries
Phil Smith
external usenet poster
 
Posts: 254
Default 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  
Old November 25th, 2009, 01:23 AM posted to microsoft.public.access.queries
Phil Smith
external usenet poster
 
Posts: 254
Default 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  
Old November 25th, 2009, 02:45 AM posted to microsoft.public.access.queries
Ken Snell
external usenet poster
 
Posts: 177
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.