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
|
|||
|
|||
Year to Date subquery
HI I would like assistance with creating a year to date subquery. I tried to
copy the MS help example but still can't get the query to run. I have the following: Table name Orders Field Names- [ID] ( system generated primary key) , [OrdersDate], [Quantity], [UnitPrice] Query SQL - SELECT Year([Orders].[OrderDate]) AS TheYear, Month([Orders].[OrderDate]) AS TheMonth, Sum([Orders].[Quantity]*[Orders].[UnitPrice]) AS MonthAmount, (SELECT Sum(dupe.Quantity * dupe.UnitPrice) AS YTD FROM Orders AS A INNER JOIN [Orders] AS dupe ON orders.ID = dupe.ID WHERE orders.OrderDate = DateSerial(Year([Orders].[OrderDate]),1,1) AND orders.OrderDate DateSerial(Year([Orders].[OrderDate]), Month([Orders].[OrderDate]) + 1, 1)) AS YTDAmount GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]); Thanks in advance for any help |
#2
|
|||
|
|||
Year to Date subquery
Please explain "can't get the query to run". Does that mean it errors? or
that it returns the wrong records? or something else is a problem? -- Ken Snell http://www.accessmvp.com/KDSnell/ "ram" wrote in message ... HI I would like assistance with creating a year to date subquery. I tried to copy the MS help example but still can't get the query to run. I have the following: Table name Orders Field Names- [ID] ( system generated primary key) , [OrdersDate], [Quantity], [UnitPrice] Query SQL - SELECT Year([Orders].[OrderDate]) AS TheYear, Month([Orders].[OrderDate]) AS TheMonth, Sum([Orders].[Quantity]*[Orders].[UnitPrice]) AS MonthAmount, (SELECT Sum(dupe.Quantity * dupe.UnitPrice) AS YTD FROM Orders AS A INNER JOIN [Orders] AS dupe ON orders.ID = dupe.ID WHERE orders.OrderDate = DateSerial(Year([Orders].[OrderDate]),1,1) AND orders.OrderDate DateSerial(Year([Orders].[OrderDate]), Month([Orders].[OrderDate]) + 1, 1)) AS YTDAmount GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]); Thanks in advance for any help |
#3
|
|||
|
|||
Year to Date subquery
Firstly the outer query is missing a FROM clause. Instead you appear to be
trying to join the instance of the Orders table in the subquery to another instance of it, which is not how its done. I'd have thought the following is what you want, differentiating between the two instances of the Orders table in the outer query and subquery by giving them aliases O1 and O2, and correlating the subquery with the outer query firstly on the year of the order date, and then on the current and prior months within the year: SELECT YEAR(OrderDate) AS TheYear, MONTH(OrderDate) AS TheMonth, SUM(Quantity * UnitPrice) AS MonthAmount, (SELECT SUM(Quantity * UnitPrice) FROM Orders AS O2 WHERE YEAR(O2.OrderDate) = YEAR(O1.OrderDate) AND MONTH(O2.OrderDate = MONTH(O1.OrderDate)) AS YTDAmount FROM Orders AS O1 GROUP BY YEAR(OrderDate), MONTH(OrderDate); I've assumed a YTD definition as the period from the start of the calendar year to the end of the month in question. Ken Sheridan Stafford, England ram wrote: HI I would like assistance with creating a year to date subquery. I tried to copy the MS help example but still can't get the query to run. I have the following: Table name Orders Field Names- [ID] ( system generated primary key) , [OrdersDate], [Quantity], [UnitPrice] Query SQL - SELECT Year([Orders].[OrderDate]) AS TheYear, Month([Orders].[OrderDate]) AS TheMonth, Sum([Orders].[Quantity]*[Orders].[UnitPrice]) AS MonthAmount, (SELECT Sum(dupe.Quantity * dupe.UnitPrice) AS YTD FROM Orders AS A INNER JOIN [Orders] AS dupe ON orders.ID = dupe.ID WHERE orders.OrderDate = DateSerial(Year([Orders].[OrderDate]),1,1) AND orders.OrderDate DateSerial(Year([Orders].[OrderDate]), Month([Orders].[OrderDate]) + 1, 1)) AS YTDAmount GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]); Thanks in advance for any help -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Year to Date subquery
Oops! Missed a closing parenthesis:
SELECT YEAR(OrderDate) AS TheYear, MONTH(OrderDate) AS TheMonth, SUM(Quantity * UnitPrice) AS MonthAmount, (SELECT SUM(Quantity * UnitPrice) FROM Orders AS O2 WHERE YEAR(O2.OrderDate) = YEAR(O1.OrderDate) AND MONTH(O2.OrderDate) = MONTH(O1.OrderDate)) AS YTDAmount FROM Orders AS O1 GROUP BY YEAR(OrderDate), MONTH(OrderDate); Ken Sheridan Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#5
|
|||
|
|||
Year to Date subquery
Thank you for responding Ken Sorry for the late reply. I was able to use the
answer posted by Ken. Thank you for your time "Ken Snell" wrote: Please explain "can't get the query to run". Does that mean it errors? or that it returns the wrong records? or something else is a problem? -- Ken Snell http://www.accessmvp.com/KDSnell/ "ram" wrote in message ... HI I would like assistance with creating a year to date subquery. I tried to copy the MS help example but still can't get the query to run. I have the following: Table name Orders Field Names- [ID] ( system generated primary key) , [OrdersDate], [Quantity], [UnitPrice] Query SQL - SELECT Year([Orders].[OrderDate]) AS TheYear, Month([Orders].[OrderDate]) AS TheMonth, Sum([Orders].[Quantity]*[Orders].[UnitPrice]) AS MonthAmount, (SELECT Sum(dupe.Quantity * dupe.UnitPrice) AS YTD FROM Orders AS A INNER JOIN [Orders] AS dupe ON orders.ID = dupe.ID WHERE orders.OrderDate = DateSerial(Year([Orders].[OrderDate]),1,1) AND orders.OrderDate DateSerial(Year([Orders].[OrderDate]), Month([Orders].[OrderDate]) + 1, 1)) AS YTDAmount GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]); Thanks in advance for any help . |
#6
|
|||
|
|||
Year to Date subquery
Thank you for your tim Ken. This worked just as I wanted. Thanks again for
your time "KenSheridan via AccessMonster.com" wrote: Firstly the outer query is missing a FROM clause. Instead you appear to be trying to join the instance of the Orders table in the subquery to another instance of it, which is not how its done. I'd have thought the following is what you want, differentiating between the two instances of the Orders table in the outer query and subquery by giving them aliases O1 and O2, and correlating the subquery with the outer query firstly on the year of the order date, and then on the current and prior months within the year: SELECT YEAR(OrderDate) AS TheYear, MONTH(OrderDate) AS TheMonth, SUM(Quantity * UnitPrice) AS MonthAmount, (SELECT SUM(Quantity * UnitPrice) FROM Orders AS O2 WHERE YEAR(O2.OrderDate) = YEAR(O1.OrderDate) AND MONTH(O2.OrderDate = MONTH(O1.OrderDate)) AS YTDAmount FROM Orders AS O1 GROUP BY YEAR(OrderDate), MONTH(OrderDate); I've assumed a YTD definition as the period from the start of the calendar year to the end of the month in question. Ken Sheridan Stafford, England ram wrote: HI I would like assistance with creating a year to date subquery. I tried to copy the MS help example but still can't get the query to run. I have the following: Table name Orders Field Names- [ID] ( system generated primary key) , [OrdersDate], [Quantity], [UnitPrice] Query SQL - SELECT Year([Orders].[OrderDate]) AS TheYear, Month([Orders].[OrderDate]) AS TheMonth, Sum([Orders].[Quantity]*[Orders].[UnitPrice]) AS MonthAmount, (SELECT Sum(dupe.Quantity * dupe.UnitPrice) AS YTD FROM Orders AS A INNER JOIN [Orders] AS dupe ON orders.ID = dupe.ID WHERE orders.OrderDate = DateSerial(Year([Orders].[OrderDate]),1,1) AND orders.OrderDate DateSerial(Year([Orders].[OrderDate]), Month([Orders].[OrderDate]) + 1, 1)) AS YTDAmount GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]); Thanks in advance for any help -- Message posted via http://www.accessmonster.com . |
Thread Tools | |
Display Modes | |
|
|