Calculated value in a table

[Rasa Read]

I need to save a calculated field from a form into a table to make it permanent piece of data. I know that violates one of the "normalcy" rules, but don't know any other way of achieving what i'm trying to do. I developed a job tracking and billing type of db - invoice total gets calculated from the data from multiple tables and forms and it gets updated if any data in the source table is changed (i.e. a price of a part goes up). However, once the customer recieves the invoice, the total is permanent to them! I need to make it permanent in my db as well. If somebody can help me - that would be great... i do some very primitive/lame vb coding, but i'm slowly learning... thanks, rasa

 

[Richie]

How do you know when the invoice has been rec'd? Do you mean when issued or when the details are recorded on your db. If say a part cost can change store the cost as a single item in another table and set the default value on the form to DLookup that value it will then automatically reflect the cost at the date of issue, any change to the cost will be reflected only on new invoices.
HTH

 

[Atomic Shrimp]

If you are storing the invoice part/product breakdown when the invoice is generated, I would be inclined to store the unit cost of the items with along that invoice detail data, or at maybe the extended line cost, that is then a fixed cost and if you need to extract the invoice data again, the total cost can be calculated from the sum of the line costs, and these line costs will reflect the cost at the point of original invoice.

HTH

Mike

 

[Rich@ITTC]

Hi Rasa

I agree with the suggestions made by Richie and Mike. You do not want to store the total cost because (as you know) it contravenes one of the normalisation rules, and those rules are based on very sound, logical principles.

Imagine if you did store the total cost somewhere in a table and then discovered that you invoice was wrong due to human input error (the customer wanted 30 not 13 of that item). Your items would no longer add up to the total cost.

You need to have a table that stores current retail prices. The order lines are stored in a seperate table with a field for the actual retail price.When an order is entered each order line draws upon the current retail price table as a default lookup value - probably using DLookUp. The actual price is stored for each item/order line as a fixed price for the item. Any trade discount or special offers (10% off in low season) can then be calculated via a query.

When retail prices are up-dated (in line with inflation or whatever) the value of each item within the orders already generated would not be affected. All new invoices would draw, via DLookup, on the new retail price.

I hope this helps clarify how to create a workable invoicing system.

Rich