Storing a Calculated Result | Not Using Calculated Fields

[MSingh]

Hi,

I know its best practice that calculate values belong in queries (or text boxes in forms/reports).
However, I need to create Statements of Account that would show eg.
Date | Document Number | Amount

Document Number would be prefixed either by INV (Invoice), CRN (Credit Note) or DRN(Debit Note), therefore "Amount" could be + or -.

I have the following 2 tables (and forms: frmSales & fsubSalesLineItems);

tblSales:
SalesID AutoNumber
OrderID Long Integer
SalesDocumentTypeID Long Integer
InvoiceNumber Long Integer
CreditNoteNumber Long Integer
DebitNoteNumber Long Integer
SalesDate Date/Time
SalesDetails Text
SalesOtherAmount
DerivedOutputVATTotalAmount
DerivedSalesTotalAmount

tblSalesLineItems:
SalesLineItemsID AutoNumber
SalesID Long Integer
ShippingExpenseID Long Integer
VATRate
SalesLineItemQuantity
SalesLineItemDescription
SalesLineItemUnitCost
SalesLineItemDiscountPercentage
SalesLineItemDerivedOutputVAT
SalesLineItemDerivedTotalAmount
SalesLineItemDerivedTotalAmountProcessed
SalesLineItemOtherDetails

The fields in boldtext store values, calculated in code in the AfterUpdate event of the textboxes that could affect the value being stored.

My question is:
Would this design be creating problems?
Noting also that tblSales.DerivedSalesTotalAmount is the sum total of all line items for the respective tblSalesLineItems.SalesID

Your advices are greatly appreciated.

Kind Regards
Mohamed

[HansV]

There are situations in which it makes sense to store derived values in a table, for instance if you're required to save a physical record of the situation at a particular moment in time, or if a value will normally be calculated, but might be modified manually in specific circumstances, or if recalculating the values would take an inordinate amount of time.
I can't judge whether it's essential for you to stored derived data, that's up to you. But keep in mind that storing a total is risky - if records in tblSalesLineItems were edited directly in the table, or in a form that doesn't have the necessary code, the total in tblSales would not be updated.

[MSingh]

Hi Hans,
Thanks again for the very prompt response, your advices are taken very seriously!
The user doesn't have access to the tables, can only write to them via forms BUT
I will avoid storing the total of tblSalesLineItems.SalesID in tblSales.
Kind Regards,
Mohamed