Query to add 2 different record values

Optionally you could use the VBA Domain function DSum to get your running
totals. This is probably a lot simpler to do and unless you have a really
large set of records the performance should be adequate.

SELECT ReadMonth
, RainAvg
, ActualRain
, DSum("RainAvg","[YourTableName]","Year(ReadMonth)=" & Year(ReadMonth) & "
AND Month(ReadMonth) =" & Month(ReadMonth)) as TotalAvg
, DSum("RainTotal","[YourTableName]","Year(ReadMonth)=" & Year(ReadMonth) & "
AND Month(ReadMonth) =" & Month(ReadMonth)) as TotalTotal
FROM [YourTableName]

That query can be built in design view
== Add your table
== Add the fields ReadMonth, RainAvg, and ActualRain to the list of fields
== In a field "box" enter
TotalAvg: DSum("RainAvg","[YourTableName]","Year(ReadMonth)=" &
Year(ReadMonth) & " AND Month(ReadMonth) =" & Month(ReadMonth))
== In another field "box" enter
DSum("RainTotal","[YourTableName]","Year(ReadMonth)=" & Year(ReadMonth) &
" AND Month(ReadMonth) =" & Month(ReadMonth))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John Spencer wrote:

After a bit of alteration to the SQL statement you provided, I have now
finally got what I wanted thank you.
This is the SQL which does me fine now:

SELECT Table1.ReadMonth,
Table1.RainAvg,
Table1.ActualRain,
Sum(Table1_1.RainAvg) AS TotalAvg,
Sum(Table1_1.ActualRain) AS TotalTotal
FROM Table1 INNER JOIN Table1 AS Table1_1 ON
Table1.ReadMonth=Table1_1.ReadMonth
GROUP BY Table1.ReadMonth, Table1.RainAvg, Table1.ActualRain;

Thanks again for your help

John Spencer wrote:
You should not normally store anything you can calculate in a query.

The query you need would look something like the following.
Unfortunately you cannot build this query using the query design view
but must use the SQL view.

SELECT A.ReadMonth
, A.RainAvg
, A.ActualRain
, Sum(B.RainAvg) as TotalAvg
, Sum(B.RainTotal) as TotalTotal
FROM [YourTableName] as A INNER JOIN [YourTableName] As B
ON Year(A.ReadMonth) = Year(B.ReadMonth)
AND A.ReadMonth = B.ReadMonth
GROUP BY A.ReadMonth
, A.RainAvg
, A.ActualRain

You can get a running start on this in query design view.
== Add your table to the query two times
(the second instance will have "_1" appended to the name
== Add the fields ReadMonth, RainAvg, and ActualRain from the first table
== Add RainAvg and ActualRain from the 2nd table
== Drag from ReadMonth in first table to ReadMonth in the second table
== Select View: Totals from the menu
== Change GROUP BY to SUM under RainAvg and ActualRain fields from the
second table
== NOW Select View: SQL from the menu
== You will need to modify the On clause of the JOIN statement to read
similiar to the above.
== Once you have that try running the query and check your results. If
your sums are wrong change AND A.ReadMonth = B.ReadMonth to AND
A.ReadMonth = B.ReadMonth (A and B) correspond to YourTableName and
YourTableName_1


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Bob H wrote:
Ok, sorry I though I had told you the feild names in a previous post,
but here they are again: I have removed TheYear feild.

ID AutoNumber
ReadMonth PK (Date type feild as 01/01/2000)
RainAvg : this is the forecasted average raifall for each month here
RainTotal : This is an accumalated amount from each month's average
ActualRain : This is the actual amount of rainfall here
TotalRain : This is the accumalated amount from each month

I have RainAvg and RainTotal to give/show what is forecasted for the
year, then ActualRain and Total Rain shows me what reall fell, and is
accumalated for the year.

I have entered some data for the RainAvg for each month, and I would
like a query to add up the accumalated amount and put that figure in
the RainTotal feild.
Also, the same with ActualRain and TotalRain feild.

I appreciate I am going to have to enter some data, but ideally I'd
like a query to do the calculations for me.

Thanks

John Spencer wrote:
Since you did not tell me any field names, I made up generic names.

If you are entering the data by hand, you might consider changeing
the design of your table to something better for your purposes.

ReadMonth: Use a date field here. Simply enter the dates as the
first day of the month and year. Since you apparently have 1 reading
per month (and only one site/area) you should be able to use this as
the Primary Key field.
RainAvg and RainActual: A number field of a non-integer type (or
currency field)

Further on your questions about field names:
A.TheYear refers to TheYear field in instance A of the table.
B.RainAvg refers to RainAvg field in instance B of the table.

Two instances of the table (think of them as a temporary copy) are
created in the FROM clause of the query by
[YourTable] as A
and
[YourTable] as B

By the way, if you are just going to generate a printed (or viewed)
report of the data, you can use a much simpler query and have the
report do the running sums over groups.

Assuming that you store an actual date in ReadMonth, your query would
look like:
SELECT ReadMonth, RainAvg, RainActual
FROM [your table]

You would use the reports sorting and grouping to group on the year
of ReadMonth and two controls in the report set to sum rainavg and
Rainactual over the group.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Bob H wrote:
Ok thanks for that below, but what is A.TheYear, A.ID and
B.RainAvg;are they the feild names I have to use.
I have added a TheYear feild to the table and entered a bit more data.
So I have so far 12 records for TheYear 2000, starting with January
through to December. I just want to test the query before I enter
more data.

Thanks

John Spencer wrote:
Assuming that ID is sequential and puts the rows in order you
should be able to use something like the following query.

You will need the year if you want the running sums by year. If
you want the running sums over the entire period without regard to
the year then remove ALL references to the field I called TheYear.

SELECT A.TheYear, A.ReadMonth, A.RainAvg, A.RainActual
, SUM(B.RainAvg) as RainAvgTotal
, SUM(B.RainActual) as RainActualTotal
FROM [YourTable] as A LEFT JOIN [YourTable] As B
ON A.ID = B.ID
AND A.TheYear = B.TheYear
GROUP BY A.TheYear, A.ReadMonth, A.RainAvg, A.RainActual
ORDER BY A.ID

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have read a little about DSUM, and I think I'll have a try at that
just to see how it works, buat as per my reply to your previous post I
hav egot it working now.

Thanks

John Spencer wrote:
Optionally you could use the VBA Domain function DSum to get your
running totals. This is probably a lot simpler to do and unless you
have a really large set of records the performance should be adequate.

SELECT ReadMonth
, RainAvg
, ActualRain
, DSum("RainAvg","[YourTableName]","Year(ReadMonth)=" & Year(ReadMonth)
& " AND Month(ReadMonth) =" & Month(ReadMonth)) as TotalAvg
, DSum("RainTotal","[YourTableName]","Year(ReadMonth)=" &
Year(ReadMonth) & " AND Month(ReadMonth) =" & Month(ReadMonth)) as
TotalTotal
FROM [YourTableName]

That query can be built in design view
== Add your table
== Add the fields ReadMonth, RainAvg, and ActualRain to the list of fields
== In a field "box" enter
TotalAvg: DSum("RainAvg","[YourTableName]","Year(ReadMonth)=" &
Year(ReadMonth) & " AND Month(ReadMonth) =" & Month(ReadMonth))
== In another field "box" enter
DSum("RainTotal","[YourTableName]","Year(ReadMonth)=" &
Year(ReadMonth) & " AND Month(ReadMonth) =" & Month(ReadMonth))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John Spencer wrote: