DB tracks yearly records, this year's values depends on last year'

[Tom Mackay]

I am looking for advice on how to complete an action in the context of an
Append Query.

My database stores financial information for customers on a year-to-year
basis. Each customer has a unique id, and when combined with the calendar
year, unique keys are formed for all the data, which can then be used to
easily retrieve date either by (i) customer, all years or (ii) by specific
year, all customers.

At the completion of each year, a new row of data is appended for every
customer. Most of the data is completely new, and is easy to add. But there
are two elements I want to add which releate to the previous year, which I
cannot figure out how to do....yet. ;-)

1) I want to take a value from the previous year for each customer, and add
it to this year's record in another location. The values in question are
Ending Value and Beginning Value..that is, last year's End Value for every
customer becomes this year's Beginning Value.

2) The second need is very similar. This time, I want to update a Running
Total value...it is the sum of last year's Running Total plus a new value
from this year.

I am trying to figure out how I can use the unique customer id and the year
to identfy the records I need in order to complete the operation...but no
luck so far. Any help would be appreciated.

Ciao,
Tom

 

[Tom Mackay]

FYI...this is a duplicate question to the one "above" it...I thought that
this one did not post successfully. If you respond to this one, you can
ignore the other. Txs, Tom

 

[Bruce Meneghin]

How about this:
Make a copy of the table you want to append to (copyTable1 in example).
Get the information from that table with query named getEndVal

SELECT copyTable1.custID, First(copyTable1.endValue) AS FirstOfendValue
FROM copyTable1
WHERE (((copyTable1.year)=2006))
GROUP BY copyTable1.custID;

Append to original table (Table1) with append query:

INSERT INTO Table1 ( custID, begValue, [year] )
SELECT getEndVal.custID, getEndVal.FirstOfendValue, "2007" AS Expr1
FROM getEndVal;

You could make the WHERE clause and Expr1 a parameter to make it more
flexible.