query problem

[Guest]

I have made a query to compute how many Items Of Stock I currently have,
using the SQL below:

SELECT tblProducts.ProductCode,
Sum(tblProducts.Quantity)-NZ(Sum(tblTransactionDetails.Quantity),0) AS
QuantityInStock
FROM tblProducts LEFT JOIN tblTransactionDetails ON tblProducts.ProductCode
= tblTransactionDetails.ProductCode
GROUP BY tblProducts.ProductCode;

The query has only worked once reducing quantities using the value from
'tblProducts'. But when I try and work it now the query produces results
larger than the value in 'tblProducts' e.g. Original Quantity = 18, when
Transactions have gone through, the query has computed the Items In Stock =
238.
Can anyone help me figure out what the problem is?

 

[David F Cox]

NZ(Sum(tblTransactionDetails.Quantity),0)

at first glance I would have:

SUM(NZ(tblTransactionDetails.Quantity,0))

 

[David F Cox]

Sorry, I've got to stop trying to solve problems "at first glance". SUM
handles NULLjust fine.

But you do have

Sum(tblProducts.Quantity)-

effectively multiplying it by the number of transactions.
Yyou only want it once, I would guess:

First(tblProducts.Quantity)-Sum(tblTransactionDetails.Quantity)

 

[Guest]

Hi. I need QuantityInStock to be continually updated because when this figure
falls below 25% of the original Quantity, a record will be added to a table
to order more stock.
I had a look at the problem more closely and found that each time a new
transaction was done it would add the original quantity to the
QuantityInStock, and then subtracted how many products were bought in that
transaction.
To overcome this I need the SQL to change and I may need a new query.
I only need the query to use the original quantity once in the expression,
after one transaction has been made, the original quantity in the expression
must be replaced with how many Items In Stock there were e.g.
[QuantityInStock] - Sum(tblTransaction Details.Quantity). But this expression
can't be done because the query is trying to find how many items there are in
stock.
I'm thinking along the lines of a query to produce the new expression above,
where QuantityInStock would be a field in tblProducts. Once the expression is
carried out I could perhaps use an update query to copy the value back into
the table.

Am I thinking along the right tracks? If yes, could you please give me some
help with the UPDATE query? Or, Is there an easier way?