query to display items low on stock (1 Viewer)

[Mark-BES]

I have a small stock control database built around a Northwind example. I am trying to build a query that displays items low on stock.
On my products form I have:
Min Order Level field (stored value)
Units on Hand (calculated value from products subform (units received-units sold)).

Q1: Do I use a query to calculate/display this?
Q2: How do I construct an expression to acheive this?

Any help much appreciated

 

[Guus2005]

Q1: Yes you do
Q2:

select * from table1 where minorderlevel => unitsonhand

 

[Mark-BES]

Many thanks for your input, however I am experiencing difficulties.
Please see the attached. This is one example of what I have tried...

 

[Simon_MT]

I would suggest that your Units on Hand is calculated within a Query and the SubForm field is bound to this value. I don't think you need [Products].[ProductID]=[ProductID] and here I would suggest creating a report based on your Query.

Simon

 

[Guus2005]

You could create a subquery:

select productID, sum([Product On Hand]) as SumOfProductOnHand from table1

This query gives you the total [Product On Hand] for each ProductID.
Store this query as qryProductOnHand. Create a second query

Select tblP.*, qryPOH.SomOfProductOnHand from Products tblP inner join qryProductOnHand qryPOH on tblP.ProductID = qryPOH.ProductID where minorderlevel >= qryPOH.SumOfProductONHand

Be creative!