Running sums in Reports

[Guest]

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

 

[Vincent Johns]

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.

 

[Guest]

Thanks Vincent,

The key to my understanding on this problem was the Inner Join function. I
used your advice and advice from other discussion groups and I think I got it
now. It's hard for a newbie to find these functions.

Jared

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

 

[Vincent Johns]

Thanks Vincent,

The key to my understanding on this problem was the Inner Join function. I
used your advice and advice from other discussion groups and I think I got it
now. It's hard for a newbie to find these functions.

Jared

I don't blame you for expecting the "Running Sum" property to work as
you expected, but even without that, Queries can sometimes do amazing
things. I'm pleased you found what you wanted.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Hi Vincent,

I wrote what I think is a very nice query to do what I do.
I have two identical queries (CHU 1 and CHU 2) that take the data without
CHU and calulates it then I have the master query that uses the two queries
and calculates a running total.

Here is the master query SQL:

SELECT [CHU 1].StationName, [CHU 1].Date, [CHU 1].Month, [CHU 1].MaxT, [CHU
1].MinT, [CHU 1].CHU, Sum([CHU 2].CHU) AS [Accum CHU], [CHU 1].Rain

FROM [CHU 1] INNER JOIN [CHU 2] ON ([CHU 1].StationName = [CHU
2].StationName) AND ([CHU 1].Date >= [CHU 2].Date)

GROUP BY [CHU 1].StationName, [CHU 1].Date, [CHU 1].Month, [CHU 1].MaxT,
[CHU 1].MinT, [CHU 1].CHU, [CHU 1].Rain

HAVING ((([CHU 1].StationName)=[Enter Station Name]))

ORDER BY [CHU 1].StationName, [CHU 1].Date;

This query works prefect for calculating the running total but a new problem
came up. The heading for MaxT, MinT and Rain became CHU 1.MaxT, CHU 1.MinT
and CHU 1.Rain respectively. The StationName, Date, Month, CHU, and Accum
CHU heading are fine. If I use the AS command to change the heading, the
data in the field disappears.
If I run the master query with a query and a table or two tables, this
problem doesn't happen.
I want to be able to run this with two queries so I don't have to be saved
any excess data in the database

Any Suggestions?

Jared

 

[Vincent Johns]

Hi Vincent,

I wrote what I think is a very nice query to do what I do.
I have two identical queries (CHU 1 and CHU 2) that take the data without
CHU and calulates it then I have the master query that uses the two queries
and calculates a running total.

Here is the master query SQL:

SELECT [CHU 1].StationName, [CHU 1].Date, [CHU 1].Month, [CHU 1].MaxT, [CHU
1].MinT, [CHU 1].CHU, Sum([CHU 2].CHU) AS [Accum CHU], [CHU 1].Rain

FROM [CHU 1] INNER JOIN [CHU 2] ON ([CHU 1].StationName = [CHU
2].StationName) AND ([CHU 1].Date >= [CHU 2].Date)

GROUP BY [CHU 1].StationName, [CHU 1].Date, [CHU 1].Month, [CHU 1].MaxT,
[CHU 1].MinT, [CHU 1].CHU, [CHU 1].Rain

HAVING ((([CHU 1].StationName)=[Enter Station Name]))

ORDER BY [CHU 1].StationName, [CHU 1].Date;

This query works prefect for calculating the running total but a new problem
came up. The heading for MaxT, MinT and Rain became CHU 1.MaxT, CHU 1.MinT
and CHU 1.Rain respectively. The StationName, Date, Month, CHU, and Accum
CHU heading are fine. If I use the AS command to change the heading, the
data in the field disappears.
If I run the master query with a query and a table or two tables, this
problem doesn't happen.
I want to be able to run this with two queries so I don't have to be saved
any excess data in the database

Any Suggestions?

Jared

Did you use the AS command in SQL view, or in Query Design View? In
Query Design View, Access should have updated the references. In SQL
view, you'll need to rewrite references to [CHU 1].MaxT yourself to use
the new name.

(Query Design View is easier. Just right-click on the Table/Query name
in the upper window, choose Properties, and change its Alias property.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

I did both. When I used the AS command, the Heading had the right name but
the data went missing. I changed the Caption property and that seems to work.
What I would like to know is why this problem only happens when I use two
queries to do this and not when I use a query and a table or two tables. And
why this happens only to the MaxT, MinT and Rain fields. I would expect the
other fields to read [CHU 1].StationName, [CHU 1].Date, [CHU 1].CHU.

Does this sound like just a quirky thing about Access?

Jared

Hi Vincent,

I wrote what I think is a very nice query to do what I do.
I have two identical queries (CHU 1 and CHU 2) that take the data without
CHU and calulates it then I have the master query that uses the two queries
and calculates a running total.

Here is the master query SQL:

SELECT [CHU 1].StationName, [CHU 1].Date, [CHU 1].Month, [CHU 1].MaxT, [CHU
1].MinT, [CHU 1].CHU, Sum([CHU 2].CHU) AS [Accum CHU], [CHU 1].Rain

FROM [CHU 1] INNER JOIN [CHU 2] ON ([CHU 1].StationName = [CHU
2].StationName) AND ([CHU 1].Date >= [CHU 2].Date)

GROUP BY [CHU 1].StationName, [CHU 1].Date, [CHU 1].Month, [CHU 1].MaxT,
[CHU 1].MinT, [CHU 1].CHU, [CHU 1].Rain

HAVING ((([CHU 1].StationName)=[Enter Station Name]))

ORDER BY [CHU 1].StationName, [CHU 1].Date;

This query works prefect for calculating the running total but a new problem
came up. The heading for MaxT, MinT and Rain became CHU 1.MaxT, CHU 1.MinT
and CHU 1.Rain respectively. The StationName, Date, Month, CHU, and Accum
CHU heading are fine. If I use the AS command to change the heading, the
data in the field disappears.
If I run the master query with a query and a table or two tables, this
problem doesn't happen.
I want to be able to run this with two queries so I don't have to be saved
any excess data in the database

Any Suggestions?

Jared

Did you use the AS command in SQL view, or in Query Design View? In
Query Design View, Access should have updated the references. In SQL
view, you'll need to rewrite references to [CHU 1].MaxT yourself to use
the new name.

(Query Design View is easier. Just right-click on the Table/Query name
in the upper window, choose Properties, and change its Alias property.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

I did both. When I used the AS command, the Heading had the right name but
the data went missing. I changed the Caption property and that seems to work.

So you got it to work. Good.

Just for fun, you might want to look at the SQL for both versions to see
how they differ.

What I would like to know is why this problem only happens when I use two
queries to do this and not when I use a query and a table or two tables. And
why this happens only to the MaxT, MinT and Rain fields. I would expect the
other fields to read [CHU 1].StationName, [CHU 1].Date, [CHU 1].CHU.

Does this sound like just a quirky thing about Access?

Jared

Not necessarily. What you see might depend on how similar the original
names are.

If you think it's important enough, you might want to put together a
minimal database that exhibits the problem, and forward it to Microsoft.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

I did some more investigating and I think the problem comes from the fact
that my original tables used in the database are MaxT, MinT and Rain. If I
change the name of the table and change the names of the headings later in
the queries it works out.

I guess I have to be more original when I name things.

Jared

I did both. When I used the AS command, the Heading had the right name but
the data went missing. I changed the Caption property and that seems to work.

So you got it to work. Good.

Just for fun, you might want to look at the SQL for both versions to see
how they differ.

What I would like to know is why this problem only happens when I use two
queries to do this and not when I use a query and a table or two tables. And
why this happens only to the MaxT, MinT and Rain fields. I would expect the
other fields to read [CHU 1].StationName, [CHU 1].Date, [CHU 1].CHU.

Does this sound like just a quirky thing about Access?

Jared

Not necessarily. What you see might depend on how similar the original
names are.

If you think it's important enough, you might want to put together a
minimal database that exhibits the problem, and forward it to Microsoft.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

I did some more investigating and I think the problem comes from the fact
that my original tables used in the database are MaxT, MinT and Rain. If I
change the name of the table and change the names of the headings later in
the queries it works out.

I guess I have to be more original when I name things.

Jared

Oog! 3 Tables with names that matched the fields in them?? Yes, I can
imagine that changing those names helped! Now, some people on this
newsgroup object to using field names like [Date] on the basis that
there are functions with the same name; I don't see a problem because
you can always put brackets [] around a name to show that it's a field
(or Table) name and not a function. But there are some places where you
could specify either a field or a Table, and if you have one of each
with the same name, I can easily imagine that Access would become
confused as to what you mean.

You might want to name your Tables on the basis of where you got the
information in them, or to suggest some generic quality that their
records represent.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

I kept it simple and changed the names to [MaxT Table], [MinT Table] and
[Rain Table]. Everything is working now.

Thanks for the help.

Jared

I did some more investigating and I think the problem comes from the fact
that my original tables used in the database are MaxT, MinT and Rain. If I
change the name of the table and change the names of the headings later in
the queries it works out.

I guess I have to be more original when I name things.

Jared

Oog! 3 Tables with names that matched the fields in them?? Yes, I can
imagine that changing those names helped! Now, some people on this
newsgroup object to using field names like [Date] on the basis that
there are functions with the same name; I don't see a problem because
you can always put brackets [] around a name to show that it's a field
(or Table) name and not a function. But there are some places where you
could specify either a field or a Table, and if you have one of each
with the same name, I can easily imagine that Access would become
confused as to what you mean.

You might want to name your Tables on the basis of where you got the
information in them, or to suggest some generic quality that their
records represent.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

I kept it simple and changed the names to [MaxT Table], [MinT Table] and
[Rain Table]. Everything is working now.

Thanks for the help.

Jared

You're welcome, but I still wonder if the Tables are organized in a way
that makes them easy to use. For example, is there any connection
between a datum in [MaxT Table] and one in [MinT Table] (such as, for
example, a date or location)? If so, the values should probably share a
record in one Table, otherwise you might have to go through contortions
to get useful results from the data you've stord.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

There is a connection between the three tables, they all contain the same
stations for the same number of days. I use a Union query to bring them all
together. The three original tables are crosstab table with station names
accross the top and dates down the side.

It's a bit slow and limits how much data I can actually use, but it works!

I actually used it tonight for my work. I printed off 74 pages of weather
reports.

If I get much more data, I'll look into a way of normalizing all the data
and storing it that way. But for now three cross-tab table and a lot of
queries is doing the job.

The whole database file is about 1.07 mb. That sure beats 270 pages of Word
documents.

Jared

I kept it simple and changed the names to [MaxT Table], [MinT Table] and
[Rain Table]. Everything is working now.

Thanks for the help.

Jared

You're welcome, but I still wonder if the Tables are organized in a way
that makes them easy to use. For example, is there any connection
between a datum in [MaxT Table] and one in [MinT Table] (such as, for
example, a date or location)? If so, the values should probably share a
record in one Table, otherwise you might have to go through contortions
to get useful results from the data you've stord.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

There is a connection between the three tables, they all contain the same
stations for the same number of days. I use a Union query to bring them all
together. The three original tables are crosstab table with station names
accross the top and dates down the side.

Aha! Yes, what you're doing makes more sense now.

But I still think that you would have an easier time if you split the
crosstab out into separate records (including datum type, station, and
date). You can write Append Queries that will do that (or someone here
can help you -- I've recently posted messages on how to do it).

It's a bit slow and limits how much data I can actually use, but it works!

I actually used it tonight for my work. I printed off 74 pages of weather
reports.

If I get much more data, I'll look into a way of normalizing all the data
and storing it that way. But for now three cross-tab table and a lot of
queries is doing the job.

The whole database file is about 1.07 mb. That sure beats 270 pages of Word
documents.

Jared

Indeed!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Thank you Vincent,

I'll look into using append queries and normalizing my data. My system
works for now but I have tested with a larger sample of data and it does stop
working.
I'll look into making these improvements for next year.

Jared