Really silly Access question

[RhondaStockton]

Hi there, I'm feeling absolutely stupid today, but I'm giving up on this one...
I have a cost table, with an item description and cost field.
I'm using the lookup wizard to view the item description field in another table.
I then have to create form where the number of items required are add and then this item description has to be selected. It should then take the actual cost and multiply it by the number of items. I can't get the cost field to multiply by cost as I'm referring to the first field, and I just can't get it to multiply it by the second field.
I've even created a duplicate table (with a one-to-one relationship) with the costing table, which shows the cost instead of the description, but when selecting the description in the form, it doesn't automatically update the cost fields, I thought that the one-to-one relationship would help with this...

I will be eternally greatful if you could assist!
Thank you
ray:

 

[SydneyGeek]

The main reason is that tables don't do calculations. You need to set up a query (becuase queries DO perform calculations) and base the form on the query.
An expression like ExtendedPrice:[Item]*[Quantity]
will do the trick

Denis

 

[Abulafia]

An explanation may be better

Databases should not store calculated values. I say 'should' rather than 'don't' as there are always enterprising/determined/stubborn people out there who will stop at nothing to get calculated values into their tables.

Storing a calculated value is a violation of the Third Normal Form (3NF) of database design (read: Normalisation) which states that a table must contain no non-key dependancies).

A non-key dependancy is when one or more fields rely on the data within another field when that field being relied upon is not the Primary Key of the table. Therefore, a DateOfBirth in an Employees Table would depend on the EmployeeID (Primary key) but a field called Age would be a non-key dependancy because we can calculate the person's age from the date of birth - ergo Age depends on DateOfBirth; it's a non-key dependancy. We remove it.

Likewise with orders. If we have three fields in an Order table called Quantity, Price, and Total where a customer ordered 10 loaves of bread (Quantity) at £0.75 (Price) then we would expect to store the price (£7.50) in the Total field. However, the Total relies on the Quantity and the Price so should be removed. It could, transpire that the customer changed their mind and wanted 20 loaves of bread. If someone changes the Quantity to 20 then we are still charging the customer £7.50 rather than the £15.00 we'd expect. So, the Total field should be removed from the table.

To ensure that the information is correct, the calculation can be done on a form, a report, or - as is the most popular - in a query. This way you are guaranteed that the result is always accurate.