I've had similar problems with Running Sum. It's sometimes useful, but
I think it's not dependable. After one unpleasant encounter with it, I
said "Fooey" (or words to that purpose) and just calculated what I
wanted using a Query.
Not knowing what your data look like, I constructed the following sample
Table on which to base a Report like what you described. I then wrote 4
Queries that I think will produce the kind of data you want. You can
probably do it more concisely, but I wanted the steps to be pretty
obvious, so I just did a little bit with each Query.
[Weather] Table Datasheet View:
Weather_ID Date Station Name MaxT MinT Rain
---------- ---- ------------ ---- ---- ----
-1617634785 6/1/2005 1 85 85 0
-243408357 6/3/2005 2 95 85 0
-1005895209 6/6/2005 3 90 85 0
-1322257069 6/15/2005 1 85 55 0
-169725580 7/1/2005 1 75 75 0
-872087360 7/5/2005 2 90 75 0
367115468 7/8/2005 3 90 75 0
-1976506011 8/1/2005 1 65 65 0
-879910463 8/2/2005 2 85 65 0
-548571171 8/13/2005 3 90 65 0
1552532674 9/1/2005 1 55 55 0
1920521454 9/10/2005 2 80 55 0
1083088858 9/21/2005 3 90 55 0
First Query, based on this Table, just calculates the CHU values and
omits some irrelevant data.
[Q_1_DailyResults] SQL:
SELECT Weather.[Station Name], Weather.Date,
Month([Weather]![Date]) AS [Month],
([Weather]![MinT]+[Weather]![MaxT])/2 AS CHU
FROM Weather
ORDER BY Weather.[Station Name], Weather.Date;
[Q_1_DailyResults] Query Datasheet View:
Station Name Date Month CHU
------------ ---------- ----- -----
1 6/1/2005 6 85
1 6/15/2005 6 70
1 7/1/2005 7 75
1 8/1/2005 8 65
1 9/1/2005 9 55
2 6/3/2005 6 90
2 7/5/2005 7 82.5
2 8/2/2005 8 75
2 9/10/2005 9 67.5
3 6/6/2005 6 87.5
3 7/8/2005 7 82.5
3 8/13/2005 8 77.5
3 9/21/2005 9 72.5
Next Query calculates monthly values. (I substituted average here for
sum, to keep the numbers relatively small.)
[Q_2_MonthlyValues] SQL:
SELECT Q_1_DailyResults.[Station Name],
Q_1_DailyResults.Month,
Avg(Q_1_DailyResults.CHU) AS AvgOfCHU
FROM Q_1_DailyResults
GROUP BY Q_1_DailyResults.[Station Name],
Q_1_DailyResults.Month
ORDER BY Q_1_DailyResults.[Station Name],
Q_1_DailyResults.Month;
[Q_2_MonthlyValues] Query Datasheet View:
Station Name Month AvgOfCHU
------------ ----- --------
1 6 77.5
1 7 75
1 8 65
1 9 55
2 6 90
2 7 82.5
2 8 75
2 9 67.5
3 6 87.5
3 7 82.5
3 8 77.5
3 9 72.5
Next Query calculates running totals for each station, accumulating over
the months for that station.
[Q_3_CumulativeCHU] SQL:
SELECT Q_2_MonthlyValues.[Station Name],
Q_2_MonthlyValues.Month,
Sum(Q_MonthlyValues_1.AvgOfCHU) AS SumOfAvgOfCHU
FROM Q_2_MonthlyValues INNER JOIN Q_2_MonthlyValues
AS Q_MonthlyValues_1
ON Q_2_MonthlyValues.[Station Name]
= Q_MonthlyValues_1.[Station Name]
WHERE (((Q_MonthlyValues_1.Month)
<=[Q_2_MonthlyValues]![Month]))
GROUP BY Q_2_MonthlyValues.[Station Name],
Q_2_MonthlyValues.Month;
[Q_3_CumulativeCHU] Query Datasheet View:
Station Name Month SumOfAvgOfCHU
------------ ----- -------------
1 6 77.5
1 7 152.5
1 8 217.5
1 9 272.5
2 6 90
2 7 172.5
2 8 247.5
2 9 315
3 6 87.5
3 7 170
3 8 247.5
3 9 320
Now that we know the cumulative values, the next Query combines these
with the daily values for use in a Report.
[Q_4_ReportValues] SQL:
SELECT Q_2_MonthlyValues.[Station Name], Weather.Date,
Q_2_MonthlyValues.Month, Q_1_DailyResults.CHU,
Q_3_CumulativeCHU.SumOfAvgOfCHU
FROM (Weather INNER JOIN Q_1_DailyResults
ON (Weather.[Station Name] = Q_1_DailyResults.[Station Name])
AND (Weather.Date = Q_1_DailyResults.Date))
INNER JOIN (Q_2_MonthlyValues INNER JOIN Q_3_CumulativeCHU
ON (Q_2_MonthlyValues.Month = Q_3_CumulativeCHU.Month)
AND (Q_2_MonthlyValues.[Station Name]
= Q_3_CumulativeCHU.[Station Name]))
ON (Q_1_DailyResults.[Station Name]
= Q_2_MonthlyValues.[Station Name])
AND (Q_1_DailyResults.Month = Q_2_MonthlyValues.Month)
ORDER BY Q_2_MonthlyValues.[Station Name], Weather.Date;
[Q_4_ReportValues] Query Datasheet View:
Station Name Date Month CHU SumOfAvgOfCHU
------------ ---------- ----- ---- -------------
1 6/1/2005 6 85 77.5
1 6/15/2005 6 70 77.5
1 7/1/2005 7 75 152.5
1 8/1/2005 8 65 217.5
1 9/1/2005 9 55 272.5
2 6/3/2005 6 90 90
2 7/5/2005 7 82.5 172.5
2 8/2/2005 8 75 247.5
2 9/10/2005 9 67.5 315
3 6/6/2005 6 87.5 87.5
3 7/8/2005 7 82.5 170
3 8/13/2005 8 77.5 247.5
3 9/21/2005 9 72.5 320
Notice that some statistics are repeated, such as the first value in the
rightmost column. In your Report, you would use Sorting and Grouping to
put this into a group summary section, instead of reporting it for each
day, similarly to where you now have the not-too-useful running sum field.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Hi,
I'm creating a database to store weather data. The data is organized in a
table by Date, Station Name, MaxT, MinT and Rain.
The Reports for the data are grouped by StationName and then by Date-by-Month.
For example, I have 3 stations and four months of data, therefore I have 12
pages of reports.
On each page of the report I have a table of data and a chart of the same
data. The table and chart are each in separate sub-reports so they can be
side-by-side on the main report.
There is a calculated value based on MaxT and MinT called CHU that goes in
the table of data on the report. This is easy to do but the problem I have
is getting an Accumulating total for CHU.
The report is based on a query and CHU is calulated there. In the report I
have a text box with the daily CHU and a text box for Accum CHU. The Accum
CHU text box uses the same Control Source as CHU but it has the Running Sum
property set to Over All. The Accum CHU test box does has a running sum of
CHU but only for one month at a time.
The second page of the report that would be Station 1, Month 2, has the
Accum CHU reset to zero.
I figure this is because I use sub-reports.
What I need is to have running sum of CHU for all of the months and then
resets at the next station.
This is simple to do in Excel but I would like to be able to calulate this
on the fly in Access and not have excess data in my tables.
Any help?
Jared