Year to Date subquery

  • Thread starter Thread starter ram
  • Start date Start date
R

ram

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?
 
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 said:
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 said:
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
Click to expand...


.
Click to expand...
 
Thank you for your tim Ken. This worked just as I wanted. Thanks again for
your time

KenSheridan via AccessMonster.com said:
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
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
Click to expand...
Click to expand...
 
Back
Top