Query Help Needed

[mattc66 via AccessMonster.com]

Need some help. I have the below query that works well, except in the
following situations.

qryInsufficientStock
SELECT tblOrderDetail.OrderID, tblOrderDetail.ProductID, tblOrderDetail.
OrdQty, tblOrderDetail.ReqShipDate
FROM tblOrderDetail INNER JOIN qryInventory ON tblOrderDetail.ProductID =
qryInventory.ProductID
WHERE (((qryInventory.OnHand)<[tblOrderDetail].[OrdQty]));

qryOrdersThatCanShip
SELECT *
FROM tblOrders
WHERE (((Exists (Select OrderID FROM qryInsufficientStock as X WHERE X.
OrderID = tblOrders.OrderID))=False))
ORDER BY tblOrders.OrderDate;

First off I am trying to determine which orders cannot be filled based on my
current stock levels. The issue comes up when I have enough stock to fill
each order on its own, however I would not have enough stock to fill the
orders together.

Inventory On Hand for Widget#1 = 50

Order#1
Widget#1 OrderQty = 25
Order#2
Widget#1 OrderQty = 35

The above query would not list these order, because it thinks it can fill the
requirments. However it cannot fill them both. It can fill one or the other.
What I'd like to do is the fill the order that came in first. That would be
Order#1.

Does anyone have a suggestion on how I could handle this issue?

Thanks
Matt

 

[mattc66 via AccessMonster.com]

What if I created a temp table that consumes the inventory? I am at a loss on
what I can do to solve this issue.

Need some help. I have the below query that works well, except in the
following situations.

qryInsufficientStock
SELECT tblOrderDetail.OrderID, tblOrderDetail.ProductID, tblOrderDetail.
OrdQty, tblOrderDetail.ReqShipDate
FROM tblOrderDetail INNER JOIN qryInventory ON tblOrderDetail.ProductID =
qryInventory.ProductID
WHERE (((qryInventory.OnHand)<[tblOrderDetail].[OrdQty]));

qryOrdersThatCanShip
SELECT *
FROM tblOrders
WHERE (((Exists (Select OrderID FROM qryInsufficientStock as X WHERE X.
OrderID = tblOrders.OrderID))=False))
ORDER BY tblOrders.OrderDate;

First off I am trying to determine which orders cannot be filled based on my
current stock levels. The issue comes up when I have enough stock to fill
each order on its own, however I would not have enough stock to fill the
orders together.

Inventory On Hand for Widget#1 = 50

Order#1
Widget#1 OrderQty = 25
Order#2
Widget#1 OrderQty = 35

The above query would not list these order, because it thinks it can fill the
requirments. However it cannot fill them both. It can fill one or the other.
What I'd like to do is the fill the order that came in first. That would be
Order#1.

Does anyone have a suggestion on how I could handle this issue?

Thanks
Matt

 

[Lord Kelvan]

to be honest what you sould be doing when you create a new order is
updating the quantity in the table that stores how much you have on
hand so that when it reaches below the amount you can see these things
and when an order is canceled you add that value back into the table
then in your order table you would have a field noted as backorder
which would store a value so you can see what orders cannot be filled.

also you would after an order is completed you would delete the order
or have a completion date.

this would make more sence to do because it would remove the hassle or
priority.

to do what you want you really need to use a form and run a loop.

Regards
Kelvan