Calculated fields

[AlexL]

I have normalised some tables but it means I have a Total Cost field
in one table, a Quantity field in another and a n Item Price field in
a third table.

I want to be able to get the Total Price field to calculate
automatically but I am having trouble working out how to get that to
happen. Do I do this using a query or using the Default Value
property in table design view? Or should I just code it as an event
handler on the form?

Thanks

Alex

 

[Allen Browne]

Alex, you will need to argue more strongly than that to convince use this is
normalized.

You may be able to use the AfterUpdate event procedure of both the Quantity
and the [Unit Price] fields to execute an Update query statement to change
the Total in the 3rd table. But there's actually much more to it than that.

- The AfterUpdate event of the controls is not enough. If the record does
not get saved, the total would have been wrongly updated.

- The AfterUpdate event of the form could work. However, you don't have
access to the OldValue of the controls at this time, so you don't know if
the update is needed or not, unless you set flags in the BeforeUpdate event
of the form as well.

- You will also need to hook the AfterDelConfirm event of the form(s) where
entries are made that could affect the totals, so ensure the update happens
as well.

- If the unit price is in another table, you might want to worry about what
happens to existing records when you alter that price. Which existing
records should be altered, and which should not?

- Because the stored total could become wrong, you probably need to also
program some mechanism to correct it periodically so it does not become
gradually more wrong over time. Of couse, such a change affects existing
data and reports you have already run from the database, so that the data in
the database may no longer match the reported data at the previous times.

Are you *really* sure all the work you are taking on to maintain the
integrity of this non-normalized approach is worth the effort?

 

[AlexL]

Alex, you will need to argue more strongly than that to convince use thisis
normalized.  

You may be able to use the AfterUpdate event procedure of both the Quantity
and the [Unit Price] fields to execute an Update query statement to change
the Total in the 3rd table. But there's actually much more to it than that.

- The AfterUpdate event of the controls is not enough. If the record does
not get saved, the total would have been wrongly updated.

- The AfterUpdate event of the form could work. However, you don't have
access to the OldValue of the controls at this time, so you don't know if
the update is needed or not, unless you set flags in the BeforeUpdate event
of the form as well.

- You will also need to hook the AfterDelConfirm event of the form(s) where
entries are made that could affect the totals, so ensure the update happens
as well.

- If the unit price is in another table, you might want to worry about what
happens to existing records when you alter that price. Which existing
records should be altered, and which should not?

- Because the stored total could become wrong, you probably need to also
program some mechanism to correct it periodically so it does not become
gradually more wrong over time. Of couse, such a change affects existing
data and reports you have already run from the database, so that the datain
the database may no longer match the reported data at the previous times.

Are you *really* sure all the work you are taking on to maintain the
integrity of this non-normalized approach is worth the effort?

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have normalised some tables but it means I have a Total Cost field
in one table, a Quantity field in another and a n Item Price field in
a third table.

I want to be able to get the Total Price field to calculate
automatically but I am having trouble working out how to get that to
happen.  Do I do this using a query or using the Default Value
property in table design view?  Or should I just code it as an event
handler on the form?

Alex- Hide quoted text -

- Show quoted text -

Thanks Allen,

First a stronger argument for you regarding normalization of my
tables:

Orders, PartOrders and Items are the tables concerned. 1 order can
have many part orders. 1 item can be on many different part orders.

Here are the three tables where * means primary key:

Orders(*OrderNo, Date, AccountNo, TotalPrice)
PartOrder (*Order No, *ItemName, QuantityOfItem)
Item(*ItemName, ItemPrice)

As Items were a repeating group in the original orders table they had
been split off into PartOrders at 1nf. ItemPrice is only functionally
dependant on the Item itself so at 2nf Item and ItemPrice were removed
from the PartOrder table. 3nf involves taking customer details away
from the Orders table but that is not relevant here so I have not
included it. I more or less reworked this from a text book example
anyway so I know it must be reasonable. If i leave the ItemPrice in
the PartOrders then it gets repeated every time somebody orders a
current bun or whatever. Similarly TotalPrice is a total of the cost
of the different partorders so can not be in the PartOrders table.

Anyway - I will use your advice and look at the afterupdate event but
i think you may well be right that this is more effort than it is
worth. I value the feedback greatly though so thanks again.

 

[Allen Browne]

Here are the three tables where * means primary key:

Orders(*OrderNo, Date, AccountNo, TotalPrice)
PartOrder (*Order No, *ItemName, QuantityOfItem)
Item(*ItemName, ItemPrice)

Do put ItemPrice in the PartOrder table, as well as the Item table:
- Item.ItemPrice = the current price of the item.
- PartOrder.ItemPrice = the price actually charged at the time of the order.
It will be different (e.g. for existing orders after you change the current
price of the item.)

Do not put TotalPrice from Orders. That's the cause of the problems here.

For an example of how to assign the current item price when you select an
item, open the Northwind sample database, and look in the order details
subform at the AfterUpdate event procedure of the ProductID combo.

 

[Klatuu]

If you have a total cost field in a table, then your database is not
normalised. Calculated values should not be stored in tables.

Also, I am assuming one of the tables is an orders detail table where you
have the quantity and the other is an inventory table where the unit price
is.

The correct design would be to have the unit price and the quantity in the
order detail table. Prices change and inventory items come and go, so
looking back at an order historically would make the order appear to be
incorrect. When you are creating a new order record, you populate the the
unit price field from the inventory record and the user enters the quantity.
Now, as to the total cost, that you calculate in the Control Source property
of an unbound control using the names of the controls where the other fields
are bound. It would look something like:

=[txtUnitPrice] * [txtQty]

But, you don't store it in your table. You calculate it each time you need
to present it to the user, either on a form or in a report (invoice, P.O.,
etc)

Now, if you are using a form/subform construct which would be normal for an
order entry form, you can show the total cost on the main form by first
adding a control to the form footer of the subform to sum the control where
the line item total is calculated:
=Sum([txtLineCost])

Because the subform is a datasheet, you will not see it, so you put a
control on the main form to display the value of the control on the subform:
=[SubformControlName].[Form]![txtLineItemTot]