Finding Product Sales for the last 3 years

[ManuelLavesa]

Hello, This time I am trying to write a query for finding out the product sales for the current year + 3 years previously (e.i. 2014,2013,2012) in order to see the trend of the product sales. to do this I started a query which has the following SQL:



SubQryTotalProductSalesFor3Years

SELECT Product.ProductName, Sum(OrderDetails.QtyOrdered) AS SumOfQtyOrdered, Year(Date()) AS CurrentYear, [CurrentYear]-1 AS LastYear, [CurrentYear]-2 AS 2YearsAgo, [CurrentYear]-3 AS 3YearsAgo
FROM Product INNER JOIN (Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.fkOrderID) ON Product.ProductID = OrderDetails.fkProductID
GROUP BY Product.ProductName, Year(Date()), [CurrentYear]-1, [CurrentYear]-2, [CurrentYear]-3
ORDER BY Product.ProductName;

This query works but always pops a message box asking for the "CurrentYear" value, if I leave it blank and click ok the result is 2015 and does what is expected. Then I am using this query as a subquery in another query named Qry3yrsProductSales and it has the following SQL:

SELECT SubQryTotalProductSalesFor3Years.ProductName, SubQryTotalProductSalesFor3Years.SumOfQtyOrdered AS Quantity, SubQryTotalProductSalesFor3Years_1.SumOfQtyOrdered AS QtyYr1, SubQryTotalProductSalesFor3Years_2.SumOfQtyOrdered AS QtyYr2, SubQryTotalProductSalesFor3Years_3.SumOfQtyOrdered AS QtyYr3
FROM ((SubQryTotalProductSalesFor3Years LEFT JOIN SubQryTotalProductSalesFor3Years AS SubQryTotalProductSalesFor3Years_1 ON (SubQryTotalProductSalesFor3Years.LastYear = SubQryTotalProductSalesFor3Years_1.CurrentYear) AND (SubQryTotalProductSalesFor3Years.ProductName = SubQryTotalProductSalesFor3Years_1.ProductName)) LEFT JOIN SubQryTotalProductSalesFor3Years AS SubQryTotalProductSalesFor3Years_2 ON (SubQryTotalProductSalesFor3Years.ProductName = SubQryTotalProductSalesFor3Years_2.ProductName) AND (SubQryTotalProductSalesFor3Years.[2YearsAgo] = SubQryTotalProductSalesFor3Years_2.CurrentYear)) LEFT JOIN SubQryTotalProductSalesFor3Years AS SubQryTotalProductSalesFor3Years_3 ON (SubQryTotalProductSalesFor3Years.ProductName = SubQryTotalProductSalesFor3Years_3.ProductName) AND (SubQryTotalProductSalesFor3Years.[3YearsAgo] = SubQryTotalProductSalesFor3Years_3.CurrentYear);

This other query also pops the message box asking for the " CurrentYear" value, and then brings only the results for the current Year (2015) but not the other values. evidently I am doing something wrong but I can not figure it out. So if you could help me I will appreciate. Thank you for your help

[June7]

Cannot reference calculated field name in its own aggregate query. Apply filter to return only 4 years (current year + 3 previous). What field has the order date?

SELECT Product.ProductName, Sum(OrderDetails.QtyOrdered) AS SumOfQtyOrdered, Year(OrderDate) AS YrOrder
FROM Product INNER JOIN (Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.fkOrderID) ON Product.ProductID = OrderDetails.fkProductID
GROUP BY Product.ProductName, Year(OrderDate)
HAVING Year(OrderDate) >= Year(DateAdd("YYYY", -3, Date()))
ORDER BY Product.ProductName;

An alternative is to not do aggregate calcs in query. Build report using Sorting & Grouping features and do aggregate calcs in textboxes in group header/footer sections. This allows display of detail data as well as summary calcs.

[ManuelLavesa]

Hello june7 thank you for your reply. You are right that I forgot to put the OrderDate field. Now The OrderDate Field should have been "InvoiceDate" From Table Orders. So in your query I replaced OrderDate with 'InvoicedDate" and for the current year works but not for the other years. As for the report option I agree with you that might be simpler and easier, but I am doing it this way to place the result in a form. Thanks again for your help.

[June7]

What do you mean doesn't work for other years? The query should aggregate the data by year and return only the last 4 years. Why a form - the records will not be editable.

[ManuelLavesa]

Well as always you are right... The query did work is just that I did not noticed the years... Silly me. Well thanks again... And yes I know the records are not editable. But my father in law wants to have it so that he can see all the information about a product in a single Form and then order more or not depending on the info.. I know it sounds crazy but he wants it this way...