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
0
Utf
2/19/2010 9:17:01 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1643 Views

Similar Articles

[PageSpeed] 45

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" <ram@discussions.microsoft.com> wrote in message 
news:47ABC2E8-F875-4FB3-BAAA-0EE97D796BE7@microsoft.com...
> 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 


0
Ken
2/19/2010 10:50:04 PM
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

0
KenSheridan
2/20/2010 12:37:32 AM
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/Forums.aspx/access-queries/201002/1

0
KenSheridan
2/20/2010 12:43:43 AM
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" <ram@discussions.microsoft.com> wrote in message 
> news:47ABC2E8-F875-4FB3-BAAA-0EE97D796BE7@microsoft.com...
> > 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 
> 
> 
> .
> 
0
Utf
2/22/2010 5:14:01 PM
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
> 
> .
> 
0
Utf
2/22/2010 5:15:01 PM
Reply:

Similar Artilces:

Overlay 4 years of data as a line on 4 years of columns for several x category labels
Dear Charting aficianados, The tutorial at http://peltiertech.com/Excel/Charts/ClusterColAndLine.html is the closest example I can find to a charting conundrum I am facing, though what I am hoping to achieve is still a little different. I've made a mock up of what I'm hoping to achieve - though I have had to use drawing objects for the line series, to overlay on the columns - I'm sure there is a way to get excel to do this - do you think there is? (sheet named mock-up) A workbook with mocuk-up and my other charting attmempts can be found: http://www.savefile.com/files/2690840 T...

month & year format in two digits
Hi folks.... is this possible in excel that if I just type MM/YY without this "/" and it appears as MM/YY. For example if I type 0805 in the cell it should appears as 08/05 once entered. Help will be appreciated. Thanks Morphyus -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 http://www.cpearson.com/excel/DateTimeEntry.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Morphyus C via OfficeKB.com" <forum@OfficeKB.com> wrote in message news:5264F31820670@OfficeKB.com... > Hi folks.... > is this possible i...

Append to date & time
I have a date field that currently has only the month, day and year. I would like to add the time to it as well. The input to the append does not have the time. Can I format a time and have it as part of the date field? Thanks "Bunky" <Bunky@discussions.microsoft.com> wrote in message news:C4CBD02C-FEBC-4395-B14E-27C35E4FC032@microsoft.com... >I have a date field that currently has only the month, day and year. I >would > like to add the time to it as well. The input to the append does not have > the time. Can I format a time and have it as part of the d...

Could I install MM Delux in the next year on the new maschine?
I thing my previous mail it the above subject gonne into the net:( So I reapet the question - my MM Delux Plus expires 11.10.2009. Could I install this MM on the new maschine in the nex year - I am goin go buy the new computer with Win 7. Greetings If you have available Activations you obtained prior to 6/30/2009 and activate prior to 1/31/2011, this is correct. Oh, and be sure and do an Internet Update to get the patches prior to 1/31/2011. One strategy I would recommend considering: install the M+ in a Win7 XP VPC VHD. Then protect that VHD file very carefully so that you might be a...

formatting date
Hey, is there any way to format this date 2005 so that it only shows the year number 5 thanks If you select the cells you want in date format, then right click on them and select Format Cells and select the "number" tab, then select Category, Date, you'll see all the date formats that come with XL. I used to know how to create custom date formats, but it's been so long, I don't remember how any more. ...

Data Points regarding DATES
I have numerous graphs wherein the X Axis represents the DATE. When I scroll my mouse along the graph line (searching for data points), I often see the accompanying data as... Series 1 Point "39409" Value 209 instead of: Series 1 Point "22-Nov" Value 209 HOW CAN I AVOID THE REFERENCE ON THE DATA LINE FROM REFLECTING THE SERIAL NUMBER INSTEAD OF THE DATE ITSELF??? (such as "22-Nov".) Thanks, FLKulchar F Lawrence ...

Posting to prior years
I have the all years closed until 2006 (calendar year setup) Is there any way to post entries to 2003. This is requirement due to restatement of financials for prior years. Thanks Sunil The only way to accomplish this is to contact Microsoft Professional Services (you can do this through your GP Partner) and have them re-open your closed years. We've had them do this for a customer of ours - worked out very well. -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "nfp" <nfp@discussions.microsoft.com> wrote in message news:ADD8C19D-BBCC-43BD-987E-3AC352...

Changing Date
My Outlook seems to be stuck on last Friday. When I have gotten a new message over the weekend, it still considers that it was received on Friday August 1, 2003 at the corresponding time. Has anyone ever experienced this? ...

Year to year comparison in Money 2003
Hello. I can't seem to generate a report that would allow me to do a year by year comparison of income and spending. I'd like to have all my categories in rows, with two columns for 2002 and 2003 next to one another. Any advice? I can't seem to figure out how to do two different time frames like that. Disregard. I figured it out. Would have been weird if it weren't possible. The Report section (print, lay-out, export to Excel) could be a little better. "Iron Flatline" <tiredofvirusspam@anonymousnewsgroupreader.org> wrote in message news:%23PqlM1I4DHA.1644...

last downloaded date and balance
After I import my bank's OFX file, the balance and date under the "Downloaded:" title at the top left corner(while viewing my account), do not update to the most current information. I looked into my bank's OFX file. The XML seems have the necessary information to update the downloaded balance and date. It used to work before. It seems to have stopped updating after my bank changed formats from OFC to OFX. I am not sure though. Any help is highly appreciated. I am experiencing the exact same problem. Please let me know if you find a solution. >-----Original M...

Duplicated date
Hi It's any function of queries to check then delete the duplicate date in one field? -rgds Mohsin -- Cheers Mohsin Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1 You cannot 'delete' a field, you can delete the WHOLE record, but not a single field in a record. You can SET a field to a NULL value, though. If there is 'a', one, duplicate row, there are TWO of them. Unless you have some means to differentiate them somehow, any SQL statement would affect BOTH rows. So, basically, use an UPDATE query that wi...

Having a date column with just month and year
I have a database of publications in Access. I want to change the date published column to just month and year instead of the way it is now--date, month and year. We don't have exact dates books were published, so the date part doesn't really serve a purpose. But I can't find a way of doing this. I'm using Access 2003. Thanks, Suroor keep the data type as Date/Time, but then change the Format to: mm-yyyy that would store 01-2010, 02-2010, etc you can setup the format to whatever makes sense to you. yyyymm, yyyy- mm, mm/yyyy, mm-yy, etc. Thanks! ...

You cannot mark this year as historical year as open year balances exist for it
Hi.. Actually While loading the Data , we didn't marked the Year 2003 as historical year. Hence , when we have loaded the 2003 GL data in the company , it has not transfered the balances to Year 2004. When we try to mark the year as historical year, it is giving the message that "You cannot mark this year as historical year as open year balances exist for it." Kindly suggest how to resolve this problem. Thanks Running the General Ledger Year-End Closing process for 2003 should bring the balances forward for 2004 and make 2003 an historical year. Before performing a y...

Dates
I am working on a spreadsheet that consists of a rolling 12 mont period. Once a month has passed how do I delete it and add a new mont to the 12 month period? Please help -- ~~ Message posted from http://www.ExcelForum.com You have to provide more info on how your data is laid out. Tell us what you have and tell us what you want to have when the month changes. HTH Otto "erodri02" <erodri02.y6nsy@excelforum-nospam.com> wrote in message news:erodri02.y6nsy@excelforum-nospam.com... > > I am working on a spreadsheet that consists of a rolling 12 month > period. Once...

Restoring a DC over a year old, Event 8012.
Hi, I never usually have any issues cloning a curretn DC, however I need to restore one that is over 1 year old for our Law team. I think it has something to do with a tomestone of 60 days on the System State, what can I do? I did think about changint the servers clock before restoring. Hello Whiteford, Is that the only DC in the network? Basically you will loose all configuration since then and also all computers have to be readded to the domain, because they loose there machine password. If you have additional DCs, forget the restore that way and better install ...

Graph displaying dates alphabetically on x axis, not by date
Hello! Title says it all - I want the x axis to show dates in DATE ORDER, not alphabetical order. I've read thru the forum, can't see how to fix it - your help will be appreciated. Kind Regards, Russell. p.s. Here is the graph Row Source, should there be an ORDER BY statement in here? SELECT (Format([Date Paid],"mmm"" '""yy")) AS Expr1, Sum([Tenant Payments]. Amount) AS SumOfAmount FROM [Tenant Payments] GROUP BY (Format([Date Paid], "mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid]...

Number of the day of the year
I need a formula that will look at a date and tell me what number day it is in that year. eg 31/1/2007 would show as 31. 1/3/2008 is 61. It is sort of like a julian date but without the year part of the number. Thanks Try the below with date in cell A1 =DATEDIF(DATE(YEAR(A1),1,0),A1,"d") -- Jacob (MVP - Excel) "NDBC" wrote: > I need a formula that will look at a date and tell me what number day it is > in that year. eg 31/1/2007 would show as 31. 1/3/2008 is 61. It is sort of > like a julian date but without the year part of the numbe...

Date time picker control cannot set time from CTime mapping
Hi I have a dialog with a Date Time Picker control. Using Class-wizard i have mapped this to a CTime data member in my dialog derived class. Im trying to set this member with a date value on initialisation but it defaults to a 1970 date. The dialog class has the following member CTime mExpireDate; In the DoDataExchange there is DDX_DateTimeCtrl(pDX, IDC_DATETIMEPICKER3, mExpireDate); BOOL CActionReceivePage::OnInitDialog() { mExpireDate = rec->GetExpireDate().GetDate(); UpdateData(FALSE); } Where GetDate() returns a valid time_t value. retreiving the set value from th...

2004 Payroll Year end Closing
I tried to create the wage file for 2004 and Great Plains Tells me its already been set up. I did not create the file manually so what happen and can I run it again. has this happen to anyone else if so what do I do?-- DJohnson ------=_NextPart_0001_48A5960E Content-Type: text/plain Content-Transfer-Encoding: 7bit Are you referring to the Payroll Year End Wage File? You can view all existing wage files under Utilities > Payroll > Remove Year End Info. If 2004 is already listed here, you could remove it. Do not remove any other years that may be listed. Once 2004 has been r...

Excel
I want to record new data by date so that the progress of my spreadsheet can be easily seen. I do not want to save hundreds of copies of my spreadsheet - e.g. Save As. Can anybody help ? ...

Move "Close Year" button on Year End Closing window
It's very easy to accidentally click on this button - users have told me that they thought the label on the button said "Close Window". As clicking on the button runs the year-end close without additional warnings, it should be moved. Alternatively, a warning could ask "Are you sure you want to close the year?" ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this lin...

Year over Year Query
Hi there. I haven't worked on Access for a few years and I was asked today to help someone with a query to calculate Year over Year percentage changes. Here is the current situation: A single table has these columns (simplified) and sample data: Year TaxAmount 2006 55000 2005 50000 2004 43000 What I need to do is dynamically create another column that calculates the YOY change for each year. The formula is simple (CurrentYear TaxAmount - Previous Year Tax Amount) / Previous Year Tax Amount. Example shown here: Year TaxAmount YOYPercent 2006 55000 10.0% 2005 50000 ...

date format not working
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) all of a sudden an excel file won't format a cell as Date. Even though the rest of the file does?! <br><br>I've tried formatting the cells in question and even though it shows dd/mm/yy it still returns a random number 3029989 or something <br><br>is there someway of resetting excel. removing some preferences file or something? thanks in advance It is possible for an Excel file to "corrupt". It's rare, but when it happens, you get strange indications such as this. The first thing I w...

Project Maintenance -- End Date
One a project is set up how do we change the end date? Its in a grey box and we can't click on it. Click on the budget button and change the end date on one or more of the cost catagories. This will cause the end date on the porject maintenance window to change. "Allyson" wrote: > One a project is set up how do we change the end date? Its in a grey box and > we can't click on it. ...

Yearly Deduction Maximum being honored the following year
A situation just came up at a client, and I wanted to know if anyone knows anything about this: They closed the payroll 2008 year and ran their 1st payrun for 2009. The YTD amount on Deductions apparently didn't reset to 0 during the close. If an employee had 999.92 deducted in 2008, with a maxiumn of 1,000.00 then only 8 cents was deducted in the 1st payrun for 2009. Worse then that, if and employee had 1,400.00 YTD in 2008 then changed their maximum to 1,000.00 they got a -400.00 deduction! The pay run was done on 12/30, and the check date was set to 01/02/2009. I'm assu...