Provided by Allen Browne, December 2007. Last updated: April 2010.
In a normalized database, you must not store dependent values (such as Quantity times UnitPrice.) You use queries to give you these calculated fields.
But what if the user wants to enter the calculated total? They cannot type into the calculated control, but neither should they need to reach for a calculator to figure out the unit price.
The solution is to add an unbound text box on your form. The user then enters the total, and the software works out the value to store. The user need not even be aware they used an unbound box. To see the example for yourself, download the Techniques sample database (40kb zipped, Access 2000 or later.)
The query (above) shows the calculated fields: AmountEx, Tax, and AmountInc.
We need these numbers in each row of the form:
The table (tblTransDetail) stores only the fields that are not dependent, i.e.:
The remaining fields are calculated in the query (qryTransDetail):
The expressions explicitly round each row to the nearest cent to avoid rounding errors when summing transactions. They also explicitly typecast the result to Currency so JET treats the numbers correctly. CCur() cannot handle Null, so we use Nz() as well.
The subform (frmTransDetail) shows the rows of a transaction (sale or purchase.) For Unit, Tax, and the column totals, we changed the background color as a visual clue that you cannot type here. But it looks like you can type in AmountEx or AmountInc. How is this possible?
Since you cannot type in the calculated control, an unbound text box takes focus as soon as the user enters the AmountInc control. If the user changes the value, its AfterUpdate event fires, so we calculate the unit price and assign it to PriceEachEx.
This is very simple to use. It works like this:
The really cool part is that the unbound text box jumps forward only on the current row of the form. Consequently the form continues to show the correct values in other rows, whereas an unbound box would normally show the same value on every row of a continuous form.
The whole operation is transparent to the users. They have no inkling that an unbound box jumped in front and we performed a calculation so as to store in the correctly normalized data, not the total they entered.
The AmountEx works the same way in the sample database.
If you are struggling to design your own tables, you might want to hear more about why we chose these particular fields in these tables.
A basic rule of normalization says, "Do not store dependent data." It follows that you will not store the Amount (quantity times price each), or the Amount including tax. But what about the tax? How and where is this stored?
Consider these alternatives:
|What to store||Usage||Problems|
|Tax-inclusive amounts only||Suitable only if you do not collect taxes.||You cannot report the tax collected, nor your real profits/margins|
|Tax-exclusive amounts only||Suitable only if you believe your government's promise that, "The tax rate will never change."||If you add a fixed rate of tax in your query, you cannot cope with a tax rate change in the future.|
|Tax Amount||Complex calculations (tiered rates or provision for fluid amounts/bribes)||Breaks normalization rules (dependant on transaction values, so can be stored wrongly.)|
|Tax Rate, in the header table||Suitable only if all items are at the same tax rate.||Does not cope if some items are made wholly or partly tax exempt (e.g. unprocessed food items in Australia.)|
|Tax Rate, in the detail table||Recommended||Rounding the tax per row (to avoid summation differences) is undesirable if you have many very small transactions (e.g. 1 pencil @ $0.07.)|
So, storing the tax rate (rather than amount) with each line item is the normalized solution that copes with the most flexible range of conditions.
You may have also noticed that the sample database stores both sales and purchases in the one transaction table (tblTrans.) The TransDirection field contains -1 for sales (reducing stock), and 1 for purchases (adding stock.)
The users think of sales and purchases as completely different, so we give them separate forms: frmSale and frmPurchase. These forms are based on queries that choose only the transactions from one direction (qrySale and qryPurchase.) Both forms have a hidden text box that gives the correct Default Value for the TransDirection field.
This arrangement provides a really simple way to calculate stock levels. By multiplying the Quantity of product by the direction (-1 for outgoing, 1 for incoming) and grouping by product, it can tell you how many more you have purchased than sold. Query qryStockLevel illustrates this basic quantity-in-hand calculation.
In a similar way, the table of clients is reused to handle suppliers, carriers, employees, and so on. You enter all the people and companies in the one table (tblClient), and then indicate any special roles they may have in the related table (tblClientRole.) The advantage is that you can connect one foreign key - tblClient.ClientID - to handle sales, purchases, wages, invoices, receipts, and heaps of other things you may need in the future. For example, tblTrans.ClientID works for sales to any client (in frmSale) as well as purchases from suppliers (in frmPurchase), just by limiting the ClientID combo on frmPurchase to those clients who have the supplier role (using lqClientSupplier as its Row Source.)
|Home||Index of tips||Top|