Calculated field does not work like I need it

[MisterY]

I have a table with the field "quantity", and the "product ID". I want to calculate "sales = quantity*price" and save this amount in the table.
I know, that I can do the formula in queries or in forms, but i still want to hardsave the sales amount in the table?
how can I do it? the problem is that I dont have the "product price" in my table, but just the "product ID", and I cant retrieve anything from other tables in a caluculated field

[pbaldy]

I would save the sales price in the sales table, otherwise when you update the price in the products table you lose the historical price older sales were made at.

[Bob Fitz]

the problem is that I dont have the "product price" in my table, but just the "product ID", and I cant retrieve anything from other tables in a caluculated field

If you don't keep the product price in a table then I assume that you enter a unit price manually each time a sale is recorded. If you have a field in the sales table to save the total in then you could use the sales forms Before Update event to calculate and store the total

[CJ_London]

I have a table with the field "quantity", and the "product ID". I want to calculate "sales = quantity*price" and save this amount in the table.

you can't in a table, you need to use a form. As you have discovered, calculated fields in tables have very limited capability.

if this is for an accounting system, you should store quantity and price plus any other values such as discount and tax as well as sales value.

if a form, you could select a product using a combo (called say productid) that brings through the price - something like

SELECT ProductID, ProductName, ProductPrice FROM tblProducts

bind the combo to column1, hide columns 1 and 3 so the user only see the product name and set the controlsource to productid

in the combo after update event put code something like

me.productprice=me.productid.column(2)
me.salesvalue=me.productprice*me.quantity

[kd2017]

if this is for an accounting system, you should store quantity and price plus any other values such as discount and tax as well as sales value.

Just to clarify; you're saying it IS appropriate to save both the unit price and calculated total price in the table in an accounting system?

[CJ_London]

Just to clarify; you're saying it IS appropriate to save both the unit price and calculated total price in the table in an accounting system?

it is more to do with business rules - with finance you are dealing with the tax authorities and the like and it is important to be able to show exactly what you invoiced/paid etc.

Yes, you can just store price and quantity and next time you calculate the sales value it should come to the same value, but even a penny out...…. So store all the elements of the calculation (discounts VAT, surcharges and the like) as well as the final sum.

I would also store the total sales value in an invoice header for the same reason (often another no no for general database structure) plus it provides a check that all invoice lines have been accounted for.

[pbaldy]

I would also store the total sales value in an invoice header for the same reason (often another no no for general database structure) plus it provides a check that all invoice lines have been accounted for.

I've got an app where I did that. I ended up creating a nightly process to check for variances, because invariably a header amount will get out of sync with the detail. Any protections we may have in place code-wise are useless against the 3-finger-salute.

[CJ_London]

I agree - didn't say it was easy! Point is you send out a legal document (i.e. an invoice) it has to be stored identically for all time (well 6 years or so). With account systems once an invoice is 'printed' it cannot be allowed to change - you have to create an adjusting credit note or invoice - although interestingly (some to my knowledge) utility companies (in the UK at least) have a rolling invoice - much like a statement, which is fine until they have to backdate something or archive earlier readings - or a record becomes disconnected.

[kd2017]

@Ajax, thank you for the information. And good timing too, I think I'll tweak the design of my current project slightly. I intuitively already starting saving the summation value in the header, I call it my AuditValue, I was told by someone on this forum that it was a bad idea a few weeks ago... I do have more questions but don't want to hijack OP's thread any longer.

@MisterY, I apologise if I've derailed your thread at all. As you acknowledged in the OP saving a calculated value is usually frowned upon and as this was curious why this would be an exception. I thought about asking Ajax in a PM but thought we could all benefit from the response.

[MisterY]

thank you guys, this is really helpful to me, I appreciate your help