S
Steve
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-
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-