Query Question

[John Torres]

I am working with the Inventory Dbase and I have a “Reorder Level” field which the quantity varies. I also have a query field Qty On Hand

[PHP]Qty On Hand: Sum([Material Inventory Tran]![Qty Received]-[Material Inventory Tran]![Qty Issued])[/PHP]

I’m trying to make a query to show all "Qty On Hand" that is less than the "Reorder Level". How do I make that Statement.

Thanks
John

[janders468]

Is there only one record in your reorder table? If so then select from your on hand query where Quantity On Hand < tblReorder.Reor derLevel. For instance if you call your on hand query OnHand_qry and your Reorder table Reorder_tbl then the query would be:

Select OnHand_qry.QuantityOnHand FROM OnHand_qry, Reorder_tbl
WHERE OnHand_qry.QuantityOnHand < Reorder_tbl.ReorderLevel

[NeoPa]

Is the [Reorder Level] field found in the same recordset that the query is run from? You should really include the relevant information in the question John.

[John Torres]

Is the [Reorder Level] field found in the same recordset that the query is run from? You should really include the relevant information in the question John.

Yes- The "Reorder Level" field is in the same recordset that the query is run from. I'm having a hard time since that the "Qty On Hand" is an expression otherwise I can do some thing like "Qty On Hand<= Reorder Level" and it just doesn't work.

I'll try the first suggestion. Thank you for the reply!

[NeoPa]

No. It won't.

This is because the WHERE and HAVING clauses are processed before the SELECT clause.

You probably need something like :

...
FROM [Material Inventory Tran]
GROUP BY ???
HAVING (Sum([Qty Received]-[Qty Issued])<[Reorder Level])

[NeoPa]

If that's a bit confusing for you, pop your existing SQL code in here and we'll see what we can do for you.

[John Torres]

Here's the existing SQL Code:
Thanks again!

[PHP]SELECT [Material Inventory].[Part Number], [Material Inventory].[Alternate Part Number], [Material Inventory].Description, [Material Inventory].Rev, [Material Inventory].[Unit of Measure], [Material Inventory].Location, Sum([Material Inventory Tran].[Qty Received]) AS [SumOfQty Received], Sum([Material Inventory Tran].[Qty Issued]) AS [SumOfQty Issued], Sum([Material Inventory Tran]![Qty Received]-[Material Inventory Tran]![Qty Issued]) AS [Qty On Hand], [Material Inventory].[Reorder Level]
FROM [Material Inventory] RIGHT JOIN [Material Inventory Tran] ON [Material Inventory].MIID = [Material Inventory Tran].MIID
GROUP BY [Material Inventory].[Part Number], [Material Inventory].[Alternate Part Number], [Material Inventory].Description, [Material Inventory].Rev, [Material Inventory].[Unit of Measure], [Material Inventory].Location, [Material Inventory].[Reorder Level]
HAVING (((Sum([Material Inventory Tran]![Qty Received]-[Material Inventory Tran]![Qty Issued]))>0));[/PHP]

[NeoPa]

Try this - and notice how it's laid out to be legible and understandable. It really makes life so much easier for you.

SELECT MI.[Part Number],
       MI.[Alternate Part Number],
       MI.Description,
       MI.Rev,
       MI.[Unit of Measure],
       MI.Location,
       Sum(MIT.[Qty Received]) AS [SumOfQty Received],
       Sum(MIT.[Qty Issued]) AS [SumOfQty Issued],
       Sum(MIT.[Qty Received]-MIT.[Qty Issued]) AS [Qty On Hand],
       MI.[Reorder Level]

FROM [Material Inventory] AS MI RIGHT JOIN [Material Inventory Tran] AS MIT
  ON MI.MIID=MIT.MIID

GROUP BY MI.[Part Number],
         MI.[Alternate Part Number],
         MI.Description,
         MI.Rev,
         MI.[Unit of Measure],
         MI.Location,
         MI.[Reorder Level]

HAVING (Sum(MIT.[Qty Received]-MIT.[Qty Issued])>=MI.[Reorder Level])

[John Torres]

THANK YOU VERY MUCH!!!! I'll try it.. Sorry for slow response- just got back from work!
John

[NeoPa]

You're welcome - and no worries about any delay. We don't expect anyone to be online all the time.