Query to add 2 different record values

[Bob H]

I am trying to build a query which will add together 2 values from 2
different record going along rather than going down.
Something like this in Excel:
Add Cell B205 to Cell C205 and produce the SUM in Cell C206

So in Access 2007 I have RainAvg value field then RainTotal field for
record 1 and the total will be equal to RainAVG
In the 2nd Rrecord the RainTotal will be the sum of RainAvg in the 2nd
record plus RainTotal from the previous record.

Is there a way this can be done in a query?

thanks

 

[John Spencer]

Only if there is some field in the table to tell me that record 1 comes before
record 2. Something like a date field can be used to calculate running sums
if the date is unique for a site(?).

So do you have one or more fields that can be used to specify a unique order
for your records?

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

 

[Bob H]

The feilds I have are ID, ReadMonth, RainAvg, RainTotal, RainActual,
ToatalRain.
Regarding the feilds, what I want is to see the difference between the
average rainfall and the actual rainfall.

Going onto my initial question, I would say that the ReadMonth feild
would be a uniquen order, as in Jan, Feb, Mar etc
ReadMonth RainAvg RainTotal RainActaul TotalRain
January 59 59 29 29
February 48 107 63 92

I hope that gives the idea of what I would like, or how I want the query
to calculate the RainTotal and TotalRain. These are running totals as
well BTW.

Thanks

 

[John Spencer]

Do you have multiple years of data? If so, how do you distinguish January
2009 from January 2008?
Is ID a sequential field without gaps?

Based on the limited information you have presented and assuming that there is
only one year's worth of data in your table then you could build a query to do
what you want. On the other hand with that small amount of data it would make
more sense to do this in EXCEL.

By the way, if ReadMonth is a text field containing the names of the months
then it cannot be easily used to order the results. February (starts with F)
comes before January. December comes before every month except April and August.

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

 

[Bob H]

Yes, I have 10 years worth of data which I currently have in a Excel
spreadsheet, and the only way I could distinguish between January 2009
from January 2008 is by the actual year, which is the way it is in
Excel. Looking at it now, I'd have to have something to tell me what
year the said data was for.
ID is a sequential feild without gaps.
I just gave a small amount of data to try and give the general idea of
what I would like.

Thanks

 

[John Spencer]

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

 

[Bob H]

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

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

Yes, I have 10 years worth of data which I currently have in a Excel
spreadsheet, and the only way I could distinguish between January 2009
from January 2008 is by the actual year, which is the way it is in
Excel. Looking at it now, I'd have to have something to tell me what
year the said data was for.
ID is a sequential feild without gaps.
I just gave a small amount of data to try and give the general idea of
what I would like.

Thanks

 

[John Spencer]

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

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

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

 

[Bob H]

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

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

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

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

 

[John Spencer]

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

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

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

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

 

[John Spencer]

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:

 

[Bob H]

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

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

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

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

 

[Bob H]

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