Storing calculated values

[Woody]

I think Access is a great product and I look forward to working with it. I
am not new to database design but I am still getting up to speed on Access
proper. Thank you for whatever help you can give me.

I understand normalization issues but in this case I want to store a
calculated value in a real field in the master table of a 1 to many form.
That is: Lets say I have an form called Invoices; a master/child form. In
the master area I have the Name of a client and address etc.

In the detail area (a grid) Lets say I have 3 rows of items purchased. Each
item has an Amount column with a value of 10.00 in it. To do what I want I
need a calculated column titled TotalAmount that shows 30.00 in it,
underneath the Amount column of the grid. As the user moves from row to row
in the grid and changes values in the Amount column of each row, the
TotalAmount field should calculate automatically. But, in addition, I would
like to have a real field called InvTotal in the top or master area of the
form that gets be updated too; updated with a real number (equal to the
"TotalAmount" calculated field) whose value I will save to the database when
saving the form.

I don't understand as yet how this kind of thing is done in Access. I can
write code, but if this requires code, I'm not sure where (what event) to put
it in. Also, it is not clear to me if the Calculated TotalAmount field
should be part of the detail table (the grid) or should it be a field outside
the grid in the master, but under the Amount field of the grid.

Is there a white paper or something like that somewhere that would walk me
through this?

 

[Jeff Boyce]

Woody

Congratulations on picking up a new tool. Here's a couple suggestions about
using it...

Tables in Access are NOT spreadsheets, even though they look like them.
Tables store data, and forms (and reports) display data. Spend some time
looking into forms and the very rich event environment they offer.

Calculated values don't need to be stored (and there are good reasons not
to, primarily around the problems of keeping all the pieces in synch).
Instead, look into using queries to generate your "calculated" values. As
an important side benefit, queries work QUITE nicely as the source for forms
(and reports).

Check the various newsgroups here for specific questions.

For general how-do-I research, check the mvps.org/access website.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve]

Depending on the situation, invoices are a rare exception to storing
calculated values. Price is stored in the products table if you are not
interested in tracking price changes of products. You merely go to the form
bound to the products table when a price of a product changes and edit the
price. The problem with this is that the database loses past prices so you
need to store the price in the order details table (invoices are created
from the order details table). You NEVER store the TotalAmount as you
suggest but rather you store the quantity and price. TotalAmount is always
calculated from Quantity * Price. If you do want to track product price
changes, you need another TblProductPrice where the PK is ProductPriceID and
you store the ProductID, PriceEffectiveDate and Price. You then store
ProductPriceID in the OrderDetails table and later use ProductPriceID when
you create an invoice.

Like Jeff, everyone will tell you it is inadvisable to store calculated
values. Be careful though of advise from a Bob Quintal. He will tell you
that it is perfectly correct to store calculated values.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Jeff Boyce]

Steve

It sounds like you are advising against storing calculated values also. The
"price-at-sale" value is not calculated, but does provide a
history-of-prices, of sorts.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Woody]

Well, I appreciate the response by you and Steve but it didn't actually
answer the question. I understand normalization issues and as I mentioned, I
have been doing this for awhile (twenty years actually). I am kind of an old
duffer, but still breathing.

If I wanted to break the rule, how do I do it? For example, I created a 1:m
form using a wizard. It did all the linking for me automatically. Very
pleasant! When I go to edit the values in the child area of the form,
however, I see no way to do so. All I have is a rectangle in design view.
The wizard named this area Child30. I would have guessed I could go into
this area of the form and edit it, kind of like a subform, but it doesn't
look like I can. Maybe I need to create a subform and substitute Child30
with it.

If I figure out the above, I guess it's just a matter of (in code) getting
the value of the sum field and placing it in my field in the master. I
suppose I would cause this code to fire when leaving a field in the "many"
table that would cause the summary field to change, or when deleting a row in
the many table. Something like that I suppose/ Y/N?

 

[Jeff Boyce]

If I'm understanding correctly, you have a main form with a subform on it.
The subform is actually just another form. You can open that other form in
design view to modify it.

Regards

Jeff Boyce
Microsoft Office/Access MVP