Year to Date subquery

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
 
K

Ken Snell

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?
 
K

KenSheridan via AccessMonster.com

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...
 
K

KenSheridan via 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
 
R

ram

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...
 
R

ram

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...
 
F

felicia

Hi Ken
I can see you are an expert in this aspect from the solution you have proferred.
canm you urgently help. I have a similar request but I actually need ytd figure for each factory in my table.
Look forward to your response.
the fields in my query/table are as follows:
Month
Year
factory name
Lost time days (this month)
Lost time days(year to date)
Report date
HI I would like assistance with creating a year to date subquery. I tried to
copy the MS help example but still cannot 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
On Friday, February 19, 2010 5:50 PM Ken Snell wrote:
Please explain "cannot get the query to run". Does that mean it errors? or
that it returns the wrong records? or something else is a problem?
Click to expand...
Click to expand...
 
B

Bob Barrows

That message was posted over a year ago. There are two Kens in the thread
that I snipped: Ken Snell and Ken Sheridan. Which one were you addressing?
If Ken Sheridan, I have not seen him here for a while. Are you saying you
only want help from Ken? If so, you might have a long wait.

Just in case you might accept help from someone else, we need some
additional information. I don't understand why there are two lost time
fields in this table. Please explain what they contain, and how the data
gets into them. Are they calculated?

Are there multiple records for each factory for each month? Is that why the
ReportDate field is there? It would probably help if you showed us a few
rows of sample data and then show the desired results from that data.

PS. If "Month" and "Year" are actually the names of those fields, you should
change them: those words are reserved keywords because they are the names of
functions that can be used in queries. Using reserved keywords can lead to
errors that are very hard to diagnose.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Null needs to be shown as 0 regardless 6
Report totals off by a couple cents. 4
Crosstab subcolumn query/report heading pblm 3
Calculate MTD and YTD in a query 8
Select Subquery: To only display one year at a time 11
new to access 3
2 crosstabs 1
Strange Behaviour in Crosstab 4

Top