Question query calculation: autocalculated value + manual entered value

[RandomT]

Hello ALL,

I am strugglying deeply with this database i'm designing. OK, here's my problem: I have 3 tables: tblTransaction, tblCertificateNo and tblTransactionType

- tblTransaction table fields: transID, transDate, transParticular (dropdown from tblTransactionType), transAmount, certNo, transComment

- tblCertificateNo field: certNo, certType (new, cancelled)

- tblTransactionType table fields: transTName (FaceValue, Rollover, Transfer, EarlyRedemption, BBForward)


Now, every transaction has its own certNo especially when it's only FaceValue (let say $400 for instance). Imagine, one day, there is EarlyRedemption, where an amount will be deducted from the Facevalue, whether it's fully redeemed or partially. When it's fully redeemed, there's no problem with my calculation as the certNo will be cancelled and zero balance (minus $400), but when it's partially redeemed (let say $150), i have an issue where the balance brought (new amount $250) will have a 'new' certificateNo, which needs to be manually entered.

When it comes to creating a calculation query, the the partial amount with the cancelled certNo (Automatically calculated to (400-150=250) and the new manual amount (250) will be added and would give me a wrong total of 500 when it's supposed to be just $250.


eg: tblTransaction

transID:1
transDate: 26/4/2010
transParticular: FaceValue
transAmount:$400
certNo: 1234
transComment: First transaction

transID:4
transDate: 26/4/2011
transParticular: EarlyRedemption
transAmount: ($150)
certNo: 1234

transID: 5
transDate: 26/4/2011
transParticular: BBForward
transAmount: $250
certNo: 1268

In query, it would be:
Total: FaceValue + (EarlyRedemption) + BBForward

I have tried researching for creating new calculated field, but to no avail, tried to do procedures, but have no limited knowledge of it, tried to do macro, but nothing yet so far.

I would appreciate your help here as the certNo is affecting my calculation, once a partial redemption is done, there have to be a new certNo assigned to the balance brought forward and have to show on report also.

Please help

Thanks

 

[DJkarl]

If the total you are looking for is $250 then why are you storing the $250 as a record in the table at all? You don't seem to have a need to store a calculated value here (and it is bad practice to store calculations anyways). If you create a query and SUM the total fields 400 + (150) you will get your $250 and any future adjustments made to that cert number.

 

[RandomT]

Hi DJKarl,

I know i shouldn't store a calculated record, but because i have to assign a new certificate number to it, i have to. Certificate Number is unique for every new transaction especially when it's a new face value, new balance brought forward, new rollover amount, or a transfer amount (to another customer i should say).