Problems summing qnty of orders between dates

[Guest]

This is probably very simple, but I can't make it do what I want! HELP!!!
It's a simple order database with products, dates and quantities... what I'm
trying to do is have a query simply give me how many total of each item were
ordered between a start and an end date. I can have it sum the items as long
as I leave the date out of it. As soon as I add the date into the query, it
gives me the items by date and doesn't add the items up.

Here's the SQL...
SELECT Sum([Order Details].Quantity) AS SumOfQuantity, Products.ProductName,
Orders.OrderDate
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY Products.ProductName, Orders.OrderDate
HAVING (((Orders.OrderDate) Between [start] And [end]));

As always... I really do appreciate all the help I get from the Group! You
guys are amazing... I'd just like to build simple things and make them
work... the complex stuff is just a dream! Thanks!!!!

 

[Allen Browne]

Drag the OrderDate into the query grid a 2nd time.
In the Total row under this field, choose WHERE.
Move your criteria under this one.

If you switch your query to SQL View, you will see a WHERE clause in the
middle of the query statement, instead of a HAVING clause at the end. The
WHERE clause is applied *before* records are selected and aggreated, whereas
the HAVING clause is applied to the aggregated value. I suspect the WHERE
clause will do what you want.

It is always a good idea to declare your parameters as well. Choose
Parameters from the Query menu (in query design view), and enter 2 rows into
the dialog:
start Date/Time
end Date/Time
This helps Access interpret the values correctly. More info:
http://allenbrowne.com/ser-45.html

 

[John Spencer]

In addition to Mr. Browne's suggestion, remove the Order Date from the SELECT
clause and the Group By clause.

Parameters Start DateTime, End DateTime;
SELECT Sum([Order Details].Quantity) AS SumOfQuantity, Products.ProductName,
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate) Between [start] And [end]))
GROUP BY Products.ProductName

Drag the OrderDate into the query grid a 2nd time.
In the Total row under this field, choose WHERE.
Move your criteria under this one.

If you switch your query to SQL View, you will see a WHERE clause in the
middle of the query statement, instead of a HAVING clause at the end. The
WHERE clause is applied *before* records are selected and aggreated, whereas
the HAVING clause is applied to the aggregated value. I suspect the WHERE
clause will do what you want.

It is always a good idea to declare your parameters as well. Choose
Parameters from the Query menu (in query design view), and enter 2 rows into
the dialog:
start Date/Time
end Date/Time
This helps Access interpret the values correctly. More info:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is probably very simple, but I can't make it do what I want! HELP!!!
It's a simple order database with products, dates and quantities... what
I'm
trying to do is have a query simply give me how many total of each item
were
ordered between a start and an end date. I can have it sum the items as
long
as I leave the date out of it. As soon as I add the date into the query,
it
gives me the items by date and doesn't add the items up.

Here's the SQL...
SELECT Sum([Order Details].Quantity) AS SumOfQuantity,
Products.ProductName,
Orders.OrderDate
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY Products.ProductName, Orders.OrderDate
HAVING (((Orders.OrderDate) Between [start] And [end]));

As always... I really do appreciate all the help I get from the Group! You
guys are amazing... I'd just like to build simple things and make them
work... the complex stuff is just a dream! Thanks!!!!