subtract from all previous records

  • Thread starter Thread starter greg
  • Start date Start date
G

greg

I have a query with a calculation that totals 3 fields
and then subtracts one other field from the total of
those three. I then have the correct answer on the first
record.

For all following records I want to continue to subtract
the total from the previous records totals.

How can I do this?
Thanks for help
Greg
(e-mail address removed)
 
Dear Greg:

A query can do this using the technique of a correlated subquery.
This subquery must provide a single value, in your case the "total
form the previous records". The "correlated" part is the part that
determines which rows are included in that set of "previous records"
which must be defined in terms of sorting key columns in the query.

I you can provide the SQL of a query that produces just what you want,
but without the value you want to subtract, I can probably code this
part for you. The query you provide should ORDER BY whatever columns
define what are the "previous records". Some example data with the
proper end result you want would be most helpful in doing this.

Finally, it is not clear whether you want the last row of this to
subtract all the other rows from it, or only those within a certain
GROUP of columns. In other words, it is sometimes necessary to select
that set of "previous records" from within a GROUP having the same
values in one or more columns. If so, you must define that group for
me. Otherwise, the "previous records" will be "all the other records"
when the query gets to the last row of its results.

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