Tbl1 field * tbl2 field calculation

[davesexcel]

Hi,


Very first post here.

I am starting a very simple web DB to start with Access 2010

TblProduct has Price TblSales has TotalPrice and is equal to TblProduct.price * TblSales.Qty


I tried the expression builder and got a message about not being able to use a different table. The relationship icon is not available.

What would be the best way to get this calculation?

Thanks.

[rpeare]

do not store calculated values in your tables, it's a huge mistake and shouldn't even be allowed.

Any time you record a sale store the quantity and price (assuming that prices can change over time you want to capture the price of an item at the time of sale) then perform your total price calculation in a query, or report.

[davesexcel]

Thank you, I see what you mean.

So In a form, how would I get the TotalPrice textbox filled ?
I click the Qty textbox and select events then After Update.
How do I enter
[TotalPrice]=[Qty]*[tblProducts!].[Price]

Thanks

[alansidman]

Make sure the text box (control in Access Speak) that will hold the total price is unbound. With your form open in design view. Right click on the control. Select Properties and then the data tab. In the control source line type =[tblSale].[Qty]*[tblProducts].[Price]

alan

[davesexcel]

Thanks,
Getting closer, I am now getting "Name?" error. I suppose the formula doesn't know what price to pick?

[rpeare]

if your form is bound to your table you shouldn't need the table qualifiers if you are storing the item price at the time of purchase as well as the quantity.

you'd just need [qty]*[price].

If your price is still coming from your item list you'd have to set your data entry query to be an autolookup format (your orders table linking to the PK of the items table in a left join)