Making a calculation and where can I put it? Table? Query?

[johnjmcnaughton]

In my Inventory Database there is a list of Products. Each ProductID has transactions to reduce or add to inventory for that particular Product. One is UnitsReceived. And the other is UnitsUsed.



I would like to make the calculation to subtract the UnitsUsed from the UnitsReceived. But I want to SUM all of the UnitsReceived and UnitsUsed for each ProductID before subtracting (this would give me "Units on Hand"). This calculation is done on the Product Form right now but I would like to have "Units on Hand" in the Products List as well. (this is in Datasheet view)

The control source for Units on Hand on the form is "=Sum(nz([UnitsReceived])-nz([UnitsUsed]))". But when I try this in a query it sums ALL of the products so each item shows the same number which is in the thousands.

So is there a way to create a query that can calculate something like this?

[orange]

Why do you have to "put it" somewhere?
Why don't you calculate when needed and display the result.
General advice is do NOT store calculated values.

Here is a reference you should read -- at least the first 5 lines.
http://allenbrowne.com/appinventory.html

[johnjmcnaughton]

I would like to put it in the products list because it would be very convenient to see the list of products and how many are in stock for each product all in the same location.

Right now it is calculated on the Products Form which only shows one product at a time.

That article says "You calculate the value when you need it." So, when do I need it? Define "need".
It seems I would need it when I look at a Product in the Product Form.
Seeing it in the Products List could be more of a convienence factor. Not knowing how many we have in stock unless we open the Products Form seems inconvienent when you have hundreds of products. Could that be categorized as a "need"?

Perhaps I should try running a report that lists the products and units on hand?.....

[orange]

Sounds like an option that would work..

As for "need", no one can define what need means in your environment better than you or your front line users.