Sum in Query

[briank]

I would like to run a calculation in a series of records.
For example:
Field 1 Field 2 Field 3
0 0 30
4 3 31
6 5 32
3 6 29
So any suggestions on how to get Field 3 to take the
prior number plus Field 1 minus Field 2?
Thanks.

 

[Tom Ellison]

Dear Brian:

In order to do this, I would need to know 2 things:

- What is your definition of "prior". Given the values in one row,
how do we find which row precedes it?

- How do you arrive at the apparent initial value of 30?

I'm suggesting you need to provide a set of one or more columns that
uniquely orders these rows. That will define what "previous" means.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[briank]

Tom,
In my example the figure of 30 reflects a calculation
that takes a number from a different table and
adds/subtracts it with field1 and field2. It is the
following records that need to be changed as the field3
figure now needs to take the first record's field1 and
add/subtract record 2's field1 and field2

-- or --

Take the number 30 and add/subtract the field2 and field
3 from the second record to get 31. Then take this same
calulation and add to this record 3's data. In other
words, as the records increase so does the calculation.

It is important to note that in my real dilemma, there
will be some fluxuations between 1 and seven records.

Did I confuse you enough?
Thanks for your help. It is much appreciated.

 

[Tom Ellison]

Dear Brian:

You said, "as the records increase," which brings me back to my first
question. You appear to be considering these rows to be in a specific
order. To make things work, you must specify the order in which they
are to be placed, preferably a unique ordering. You didn't answer my
question about that as far as I can see. I cannot proceed without
this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[briank]

Tom,
The records are sorted by date in ascending order (sorry
about not mentioning this). Does this help?
Thanks again for your time.

 

[Dale Fye]

see the responses to "different value between each record" posted on
10/23/03

Then modify it to accomodate Field1 and Field2.

--
HTH

Dale Fye


I would like to run a calculation in a series of records.
For example:
Field 1 Field 2 Field 3
0 0 30
4 3 31
6 5 32
3 6 29
So any suggestions on how to get Field 3 to take the
prior number plus Field 1 minus Field 2?
Thanks.

 

[Tom Ellison]

Dear Brian:

The query can now be written:

SELECT Field1, Field2,
(SELECT SUM(Field1 - Field2) + 30 FROM YourTable T1
WHERE T1.[date] <= T.[date]) AS Field3
FROM YourTable T
ORDER BY [date]

Please pay careful attention to these notes:

- Replace YourTable in the query with the actual name of your table.

- Replace Field1, Field2, and [date] with the actual names of the
columns in your table.

- I am seriously wondering if [date] is unique. If you ever have two
rows in the table with the same [date] value then this isn't going to
work. As I stated initially, you must uniquely order the rows if you
want the running sum (your Field3) to work the way you probably expect
it to work. If you have an autonumber column or some other column
that can be included to make the ordering unique then you need to
specify it so I can make a better query here.

- I just stuck the value 30 in because you've given me no information
about how this can be derived.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts