Hello everyone,
I have the following query:
SELECT tblPurchaseOrders.PartNumber, Sum(tblPurchaseOrders.OrderedQty) AS SumOfOrderedQty, tblPurchaseOrders.OrderDate
FROM tblPurchaseOrders
GROUP BY tblPurchaseOrders.PartNumber, tblPurchaseOrders.OrderDate
HAVING (((tblPurchaseOrders.OrderDate)>=DateAdd("m",-6,Date())));
There are multiple "OrderedQty" entries in tblPurchaseOrders for each PartNumber; each with a different order date.
I want to display the total OrderedQty for each PartNumber where the date given in tblPurchaseOrders for the entry is less than 6 months old from "today".
The SUM function works perfectly as it should until I add the 6 month criteria on the date field; at which point the query returns only the 6 month orders but no longer sum's them - instead it shows each entry.
For example, PartNumber S14141 was ordered several times since the start of time, totalling 850 units. Here is the query without the date criteria:
So far, so good. But...
During the last 6 months, the part has been ordered twice - once for 50 units and once for 200. I want the query to therefore show for part S14141 the SumOfOrderedQty = 250. Instead it itemises the 6 monthly transactions:
I'm sure its a simple thing to correct but as usual, I am blind to it!
Any thoughts gratefully received as always!
Chris
I have the following query:
SELECT tblPurchaseOrders.PartNumber, Sum(tblPurchaseOrders.OrderedQty) AS SumOfOrderedQty, tblPurchaseOrders.OrderDate
FROM tblPurchaseOrders
GROUP BY tblPurchaseOrders.PartNumber, tblPurchaseOrders.OrderDate
HAVING (((tblPurchaseOrders.OrderDate)>=DateAdd("m",-6,Date())));
There are multiple "OrderedQty" entries in tblPurchaseOrders for each PartNumber; each with a different order date.
I want to display the total OrderedQty for each PartNumber where the date given in tblPurchaseOrders for the entry is less than 6 months old from "today".
The SUM function works perfectly as it should until I add the 6 month criteria on the date field; at which point the query returns only the 6 month orders but no longer sum's them - instead it shows each entry.
For example, PartNumber S14141 was ordered several times since the start of time, totalling 850 units. Here is the query without the date criteria:
So far, so good. But...
During the last 6 months, the part has been ordered twice - once for 50 units and once for 200. I want the query to therefore show for part S14141 the SumOfOrderedQty = 250. Instead it itemises the 6 monthly transactions:
I'm sure its a simple thing to correct but as usual, I am blind to it!
Any thoughts gratefully received as always!
Chris