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 Query Issue
Hi all-
Thanks in advance for taking time to give me a hand. I'm having an issue with trying to make a Year to Date function in my query. I want it to give me the year to date rainfall totals for each individual site along with historical data and rainfall of the current month. I can get everything else but the YTD to work. Here's what I have so far: SELECT Month([ldate]) AS [Month], Year([ldate]) AS [Year], wdata.sitename, Avg(wdata.rainfall) AS AvgRainfall, Max(wdata.rainfall) AS [Historical Hi], Min(wdata.rainfall) AS [Historical Lo], Max(wdata.rainfall) AS HistoricalYTD, ([YearDate])- ([HistoricalYTD]) AS Deviance, Sum(wdata.rainfall) AS HistRain, wdata.sitecode, (SELECT Sum(wdata.rainfall) FROM wdata WHERE wdata.sitename=[Forms]![Excel Rain]![lakeCombo] AND Year(ldate)=Year(Date()) And Month(ldate)=Month(Date())) AS YearDate FROM wdata, HistRain WHERE (((wdata.ldate) Between [Forms]![Excel Rain]![StartDate] And [Forms]![Excel Rain]![EndDate])) GROUP BY Month([ldate]), Year([ldate]), wdata.sitename, wdata.sitecode ORDER BY Month([ldate]), Year([ldate]); Thanks in advance for any help any of you give. -steve- |
#2
|
|||
|
|||
Year to Date Query Issue
In article 1171470146.179521.36550
@v33g2000cwv.googlegroups.com, says... FROM wdata WHERE wdata.sitename=[Forms]![Excel Rain]![lakeCombo] Does this give the year-to-date rainfall? This method is taken from Hugo Kornelis, SQL Server MVP SELECT SUM(IIF(DATEDIFF("y",DATEADD("y",DATEDIFF ("y",0,NOW),0),w.rainfall) = 0, w.rainfall,0)) FROM wdata AS w WHERE w.sitename = [Forms]![Excel Rain]![lakeCombo]; |
#3
|
|||
|
|||
Year to Date Query Issue
|
Thread Tools | |
Display Modes | |
|
|