The best way to see this might be with an example.
Open the Northwind sample database.
Choose Relationships on the Tools menu, so you can see how the tables fit
together. There is a table for Orders, and another for Order Details, since
one order can have many rows (many products ordered.) The Order Details
table has fields:
OrderID which order this line belongs to.
ProductID which product is ordered on this line of the order.
UnitPrice how much each
Quantity how many of this product
Discount a percent discount (which you can ignore.)
Now open the Orders form. it has a subform where the user enters the rows of
products ordered. The last column in the subform is named Extended Price. It
is the UnitPrice times the Quantity, less the Discount. But there was no
such field in the table. How did it get into the form?
The Orders Subform gets its records from the query named Order Details
Extended. Open that query in design view. The last column in the query is
the calculate field, made up of the UnitPrice times quantity less discount.
Because it is calculated as needed in the query, there is absolutely zero
chance of every storing the wrong amount in the table: there is *no* such
total stored in the table.
This technique illustrates one of the basic rules of data normalization:
Never store dependant data. If some value depends on the other fields (and
the total amount depends on the quantity and price each) then you do NOT
create a column for it in your table. Instead you use a calculated field in
the query, and you never have to worry about the value being wrong.
Kathleen, this is a very important concept to grasp. It is one of the things
that makes the difference between a 100% reliable database and something
that is an absolute nightmare to maintain. Don't store the calculated total:
it is a maintenance nightmare.
