Autosum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 fields in a table, one of the fields contains a "price per unit". a second field contains the quantity purchased, I want the 3rd field to aotumatically display the total amount, i.e. Price per unit * Quantity purchased. Can anyone please tell me how to do this please - ripping my hair out here :-

thnx in advanc

Stev
 
Ste Hughes said:
I have 3 fields in a table, one of the fields contains a "price per
Click to expand...
unit". a second field contains the quantity purchased, I want the 3rd field
to aotumatically display the total amount, i.e. Price per unit * Quantity
purchased. Can anyone please tell me how to do this please - ripping my
hair out here :-(

In a Database one does not do calculations *in tables*. Remove the field
from the table. Then build a query with all of the fields in the table and
add a calculated field using the expression...

Total: Nz([Price per unit],0) * Nz([Quantity purchased],0)

(The Nz() functions are there to handle null values.)

Now you simply use the query in places where you were using the table and
which require the Total field.
 
Steve,

What you are trying to do is not considered good database design. Remove
that third field, TotalAmount, as it is redundant. When you do reports and
queries, you can always do the calculation (PricePerUnit * Quantity) to show
a total.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Ste Hughes said:
I have 3 fields in a table, one of the fields contains a "price per unit".
Click to expand...
a second field contains the quantity purchased, I want the 3rd field to
aotumatically display the total amount, i.e. Price per unit * Quantity
purchased. Can anyone please tell me how to do this please - ripping my hair
out here :-(
 
You may even want to normalize that table. If you have the description
and the price per unit in the same table that you record individual sales of
the same item you may be better off with one table storing the price. Of
course then you get into complications due to price changes. Look up
normalize in the help file and look at the Northwind sample.

Good Luck
 
Back
Top