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

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

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

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

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


.

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

.