Appending records that use "last year's" records...

[Tom Mackay]

I am trying to figure out how to build an Append Query.

The append query adds new records to table that keeps track of yearly
customer financial records. The records are identified by both a unique
customer id and the year, and combined into a unique key that allows
retrieval of this data either by customer and/or by year.

Each year, new records are added for each customer. Most of the data is
brand new and easy to create and append. But there are 2 elements that rely
on the previous year's record for that customer..and I cannot figure out how
to identify last year's record for each client, and the fields I need in
order to make them part of this year's append action.

Specifically...

1) Each customer has a Begin Value and an End Value. This year's Begin
Value is equal to last year's End Value.

2) Similar. Each customer has a Running Total. This years Running Total
is equal to last year's Running Total plus this year's new business.

Any help would be most appreciated. No luck so far in using the client id
and the year to figure out how to identify the elements I need...

Thanks, Tom

 

[Tom Mackay]

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

 

[TedMi]

...
1) Each customer has a Begin Value and an End Value. This year's Begin
Value is equal to last year's End Value.

That's a *BIG* no-no. You do not store redundant data in a database. If this
year's begin is the same as last year's end, you just use last year's end.

In this case, I would recommend that you not store begin and/or end values,
but for each customer you store just the annual total for each year. And
since the annual total is presumably made up of a series of individual
transactions, you do not need to store the totals, but generate them with
queries for the current year, for years x thru y, or for all years until now.

 

[Tom Mackay]

I understand about the duplication no-no concept. But my design approach has
to be iterative....I am fairly inexperienced...or else I can't procede...once
I confirm that I know how to record a piece of info once and use it from
anywhere, then I'll follow all the best db design practices that I can.

Btw...this particular data is a point-in-time piece of info, not a sum of
transactions, so I need to record it at least once.

In that spirit...here's what I am trying to do in my append query to bring
last year's data forward into this year's record for each customer/year
combination. If the syntax can be corrected, I am happy to try and use it in
a way that each point-in-time value can be recorded only once instead of
duplicated.

BMV: (SELECT [EMV] FROM [Financials] WHERE [Sort ID] = [Betty and Bob
Financials].[Sort ID] AND [Yr] = [Financials].[Yr]-1)

BMV - begin value
EMV - end value
sort id = unique customer id
yr = calendar year

Right now this grabs nothing for BMV, ever...any advice?

 

[TedMi]

Btw...this particular data is a point-in-time piece of info, not a sum of
transactions, so I need to record it at least once.

And just what do you mean by "point-in-time"? Just the one transaction, if
any, that occured at a certain time on a certain day? Or the record of all
things that occured as of a point in time, i.e. since some earlier time? The
latter is indeed a sum of transactions, and you do not need to record it,
because you can always regenerate it, if you have a record of each individual
transaction with its timestamp.

 

[Tom Mackay]

It is the value of my customer's accounts...the accounts have holdings of
stocks, bonds, mutual funds and other more complex investments...and at a
certain point in time, the value of each individual holding is measured, and
the value of the entire account is get valued and recorded.

 

[Tom Mackay]

OK. I have managed to trap the previous year's EMV, and store it in the
current year's record as BMV by using the following in the FIELD:

BMV: (SELECT [EMV] FROM [Financials] as Tmp WHERE [Sort ID] =
[Financials].[Sort ID] AND [Yr] = [Financials].[Yr]-1)

Now, in order not to dupe the value in the db, I am trying to use this
expression whenever I require this value....

....and it seems to work!

Thanks for your responses.