How to calculate a field value dynamically in a table

[gaya123]

I have two tables. Product table has "product price" column. OrderLine table has "quantity ordered" column. I have a column "LineItemCost" in OrderLine table. The value for line item cost is (product price*quantity ordered). This needs to be updated dynamically in the OrderLine table when each row is created. Could someone help me with this????? I am totally stuck and I am not very familiar with Access..

Thanks in advance for the help!

[Robeen]

You CAN create a Calculated field in the Table.

To do this - both the UnitPrice and Quantity need to be in the same table.
If they are - then - in table design - add a field and name it TotalCost - for example.
For Data Type for this field - select 'Calculated'.
In the calculation [expression] builder type in:
[UnitPrice] * [Quantity]

I think it might be better to create a query, though, and have the calculation in the query - rather than in the table.

I hope this helps!

[gaya123]

I tried to create an update query to multiply price and quantity and get line item cost. I want to know how i could invoke this query automatically when each record is created in order line table?

[Robeen]

What exactly do you need to have happen when you create a new record?

Can you explain for me what you mean when you say 'invoke this query'?
What are you envisioning when the query is invoked?

[alansidman]

Having calculated fields in tables is a bad database practice. You should only do calculations in queries and forms. If you need the calculated information it can easily be retrieved in this manner.

More information on this

http://www.access-programmers.co.uk/...d.php?t=196906

[gaya123]

The value for lineitemcost column needs to be updated dynamically every time a record is inserted in order line table.

[Robeen]

If you use that calculated field in the Table - which is not the best thing to do - then as soon as a new record is inserted [committed] into the table - the Calculated field will become available.

Again - this is not 'best practice'.

[shafiq037]

it works good for number. but dates can not be calculated to find age of a person in this way.