Low Stock report query Question

[samos1023]

I have a inventory database that I am working on that I need to generate a low stock report query. I have most of the logic for the query working, but the final step I'm not sure about. I know logically what I need to do just not how to code it in access. The query I have counts the inventory table by productID and has another column that displays this count. The product table has a field that stores a number for the low stock warning number. These two items are in the query and currently working. I get a list of all items that have a low stock count and a column with how many are in the db. Below is my query view picture, and datasheet view picture

SQL for the query


**
SELECT tblInventory.ProductID, tblManufacturer.CompanyName, tblProducts.PartNumber, tblProducts.LowStockCount, Count(tblInventory.ProductID) AS CountOfProductID
FROM tblManufacturer INNER JOIN (tblProducts INNER JOIN tblInventory ON tblProducts.ID = tblInventory.ProductID) ON tblManufacturer.ID = tblProducts.ManufacturerID
GROUP BY tblInventory.ProductID, tblManufacturer.CompanyName, tblProducts.PartNumber, tblProducts.LowStockCount
HAVING (((tblProducts.LowStockCount)>0));
**


The second part is what i don't have working. I want the query to only return records where the count is less than the LowStockCount. I have tried a few things but no luck. How do I put that equation into my query?

[orange]

There must be more to your database than what you have shown --Customer, SalesOrder, OrderDetail,PurchaseOrder....

There is a free generic model for inventory at Barry Williams' site.
Here is info on ReOrder level
and Reorder Quantity.

Here is info re Inventory by Allen Browne that is often referenced when dealing with Inventory Management/Control.

Here is a link from Blueclaw re Inventory that may useful.
Hope it's useful to you.

[Gicu]

Just save the query you have that create a new one based on it and simply put the criteria in that one "SELECT * FROM [YourQueryName] WHERE [CountOfProductID]<=[LowStockCOunt];"

Cheers,
Vlad