Table design when working with cumulative financial data

[SimonPGreen]

Hi all,

I have a requirement to store and report on some financial data in the following format

intAppNo
dteAppDate
curAppValue

So a select * might result in

intAppNo dteAppDate curAppValue
1 30/1/2004 1000.00
2 26/2/2004 3000.00
3 20/3/2004 6000.00

The question I have is with respect to the data in curAppValue as this data represents a cumulative currency figure. When reporting I need to state the movement in each period i.e.

intAppNo dteAppDate curAppValue Movement
1 30/1/2004 1000.00 1000.00
2 26/2/2004 3000.00 2000.00
3 20/3/2004 6000.00 3000.00

How would I produce a report like this?

Is there a better way to achieve this?

Any help appreciated.

Regards,

Simon

 

[PHV]

A more usual way is to store the movement value and to get the cumulative currency in a report (or query) with a running total (aka running balance).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

To understand the concepts of moving values, you should look into 'transaction' processing. This is similar to the "old school" stacks and ques processes approach, but may also be employed in a database system. In your example, the incremental values would be stored as individula "reansaction" records. To find the "Balance" at any point would just be the sum from the beginning to the point (in time) of interest. Of course 'real world' systems using this concept require a "Transaction Date" for each entry where these would include the time component. Also, the "real world" would need to include an Account ID field and ALL transactions would be accomplished through Begin -End TRansaction blocks and ALL processes (account Transactions) would need to be confirmed.

Such systems are quite useful in tracking "busy" processes, but often require a great deal of security code.



MichaelRed

 

[SimonPGreen]

Thanks for the advice guys.

The problem I have is that the data entry has to be the cumulative value as this is the value given during the process. I suppose we could just allow the cumulative value to be entered and calculate the value written to the database field so that we store the movement?

Regards,

Simon

 

[SimonPGreen]

As an aside is it possible to build some logic into a select query that would produce a calculated column for movement as detailed above i.e.

Take curAppValue and if no previous then movement = curAppValue else movement = curAppValue - previous curAppValue.

Regards,

Simon

 

[PHV]

Provided intAppNo is chronologically numbered without hole:
SELECT C.intAppNo, C.dteAppDate, C.curAppValue, C.curAppValue-Nz(P.curAppValue,0) AS Movement
FROM tblCumul AS C LEFT JOIN tblCumul AS P ON C.intAppNo = P.intAppNo + 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SimonPGreen]

Thanks for that PH

Probably going to take me a week and some loss of hair to understand how it works though

Regards,

Simon

 

[SimonPGreen]

Unfortunately the self join doesn't help me as my description above was somewhat simplified. The data is actually pulled from three tables and I pressume you can't specify a self join in this scenario.

My select thus is

SELECT [Contracts].[strContractNumber] AS [Contract Number], [Packages].[strPackageName] AS [Package Name], [Applications].[intValuationNumber] AS [Valuation Number], IIf([CurCertificationValueReceived]>0,[CurCertificationValueReceived],IIf([curValuationValueSubmitted]>0,[curValuationValueSubmitted],[curForecastValue])) AS [Value], IIf([dtePaymentExpected]>0,[dtePaymentExpected],[dteValuationDate]+[intDefaultPaymentPeriod]) AS [Payment Date], [Applications].[dteValuationDueDate] AS [Valuation Date], ccur(IIf([CurCertificationValueReceived]>0,[CurCertificationValueReceived],IIf([curValuationValueSubmitted]>0,[curValuationValueSubmitted],[curForecastValue]))*[dblRetentionValue]) AS Retention, [Value]-[Retention] AS [Cumulative Value]
FROM (Contracts INNER JOIN Packages ON [Contracts].[PKContractRef]=[Packages].[FKContractRef]) INNER JOIN Applications ON [Packages].[PKPackageRef]=[Applications].[FKPackageRef];

Regards,

Simon

 

[PHV]

Another way is to pull the query resultset in Excel (Data, -> External data -> ...) and have a very simplistic formula down a column to retrieve the Movement amount ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

" ... the data entry has to be the cumulative ...

Emphasis added.

This serves (to me at least) as THE red-flag. If the process is this inflexible, it is either homework or the result of a more-or-less well defined DOUFUS. I bow out and 'celibrate; the patience of others remaining in the fray.



MichaelRed

 

[SimonPGreen]

OK I concede - the data need not be stored as cumulative data as it would be pretty easy to implement the ability to input cumulative values but store the movement.

Regards,

Simon

 

[lespaul]

the result of a more-or-less well defined DOUFUS

Is that an offical technical term?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual