Calculating 2 fields in a Query

[JakeMann41]

Hi All

I have a query that looks at 2 tables

OpeningStockTable and MovementsTable



I have an Expression that calculates fields within those tables - [OpeningStock]+[QtyMovement]

When I run the query I get the result I want.

However, is it possible to then change that opening qty to a new qty based on the result of the query?

[Welshgasman]

I would say no?
How can you expect to change the source of something where whether you do or not relies on the result of that source?

You might be able to use a tempvar set to that result and then amend the qdf.sql to suit, but you have to determine whatever first and not from what you wish to amend.

[ano]

u can change the output into select "update ..." with text strings for the update sql so u can run the update later

[NoellaG]

When a movement is validated, you can run an update query where you set StockQty= StockQty + MoveQty without first running a select query. That's how it's done in most of the warehouse applications. This way you always have the correct current stock qty. Or you can do it with a SQL statement in VBA.

[ano]

nice to confirm but sql not need in vba , but vba is the place to do all