Calculate difference or Change between records

[Barry A&P]

I have a report that tracks the number of items held by person.

T_People
PersonID
Name
StartingBalance

T_Balances
BalanceID
BalanceDate
Balance

My report shows a balance for each person grouped by date and a calculation
that shows % change from StartingBalance, i would like to calculate the
Difference in balance since the last date a balance was recorded for that
person "ChangeSinceLastBalance".. so my result would be,,

BalanceDate, Name, Balance, ChangeSinceLastBalance,
ChangeSinceStartingBalance.

I know this is Possibly a DMax Thing but cant figure out how

Any ideas would be appreciated

Barry

 

[Allen Browne]

To calculate the difference between the current record and the previous one
for the same customer, use a subquery.

Here's an explanation and example:
http://allenbrowne.com/subquery-01.html#AnotherRecord

Whatever you are doing here, hopefully you are aware that this is not the
right way to store values in a relational database, because it imples
dependencies between records. For example, if it were discovered that there
was an unprocessed withdrawal on an account 8 weeks ago, that would render
all subsequent records you have for that account invalid. While you might
think that unlikely, it illustrates why one of the most basic rules of data
normalization is that you never store dependent values.

The usual way to handle this data would be to store each transaction, and
have the database calculate the balances, rather than store the balances and
then try to work back to the transactions. FWIW.

 

[Barry A&P]

Allen
The sub query worked perfectly
Thanks once again for your help. I know Balances doesent seem like i am
storing data correctly. I am only monitoring a "Physical Count" of items on
hand not coming and going. so if one count was submitted incorrectly and
updated it should only affect the "Difference from previous" in that weeks
calculations. and not the big picture.

I am so glad youre here looking out for us
Barry