By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 3,102 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Query Question

P: 42
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
Aug 6 '08 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 112
Is there only one record in your reorder table? If so then select from your on hand query where Quantity On Hand < tblReorder.ReorderLevel. For instance if you call your on hand query OnHand_qry and your Reorder table Reorder_tbl then the query would be:
Expand|Select|Wrap|Line Numbers
  1. Select OnHand_qry.QuantityOnHand FROM OnHand_qry, Reorder_tbl
  2. WHERE OnHand_qry.QuantityOnHand < Reorder_tbl.ReorderLevel
  3.  
Aug 6 '08 #2

NeoPa
Expert Mod 15k+
P: 31,307
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.
Aug 6 '08 #3

P: 42
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!
Aug 7 '08 #4

NeoPa
Expert Mod 15k+
P: 31,307
No. It won't.

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

You probably need something like :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. FROM [Material Inventory Tran]
  3. GROUP BY ???
  4. HAVING (Sum([Qty Received]-[Qty Issued])<[Reorder Level])
Aug 7 '08 #5

NeoPa
Expert Mod 15k+
P: 31,307
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.
Aug 7 '08 #6

P: 42
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]
Aug 7 '08 #7

NeoPa
Expert Mod 15k+
P: 31,307
Try this - and notice how it's laid out to be legible and understandable. It really makes life so much easier for you.
Expand|Select|Wrap|Line Numbers
  1. SELECT MI.[Part Number],
  2.        MI.[Alternate Part Number],
  3.        MI.Description,
  4.        MI.Rev,
  5.        MI.[Unit of Measure],
  6.        MI.Location,
  7.        Sum(MIT.[Qty Received]) AS [SumOfQty Received],
  8.        Sum(MIT.[Qty Issued]) AS [SumOfQty Issued],
  9.        Sum(MIT.[Qty Received]-MIT.[Qty Issued]) AS [Qty On Hand],
  10.        MI.[Reorder Level]
  11.  
  12. FROM [Material Inventory] AS MI RIGHT JOIN [Material Inventory Tran] AS MIT
  13.   ON MI.MIID=MIT.MIID
  14.  
  15. GROUP BY MI.[Part Number],
  16.          MI.[Alternate Part Number],
  17.          MI.Description,
  18.          MI.Rev,
  19.          MI.[Unit of Measure],
  20.          MI.Location,
  21.          MI.[Reorder Level]
  22.  
  23. HAVING (Sum(MIT.[Qty Received]-MIT.[Qty Issued])>=MI.[Reorder Level])
Aug 7 '08 #8

P: 42
THANK YOU VERY MUCH!!!! I'll try it.. Sorry for slow response- just got back from work!
John
Aug 11 '08 #9

NeoPa
Expert Mod 15k+
P: 31,307
You're welcome - and no worries about any delay. We don't expect anyone to be online all the time.
Aug 11 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.