FIFO Help

It's the (Select ... ) part in parentheses inside the WHERE clause.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"mattc66 via AccessMonster.com" u16013@uwe wrote in message
news:89c5a93fcd5df@uwe...
How do you add a sub query?

Graham Mandeno wrote:
Hi Matt

I was thinking that you would satisfy Order 1 then requery (stock qty is
now
3) then satisfy Order 2 and requery (stock is now 2) and Order 3 would
disappear from the list.

In order to eliminate Order 3 from the list initially, you will need to
add
a subquery to qryInsufficientStock.

Your SQL would look something like this (untested!):

Select OrderDetail.OrderID, OrderDetail.ProductID,
OrderDetail.Qty, OrderDetail.Qty-Inventory.Qty as Required
from Orders inner join (OrderDetails inner join Inventory
on OrderDetails.ProductID=Inventory.ProductID)
on OrderDetails.OrderID=Orders.OrderID
where (Inventory.Qty(Select Sum(Qty) from OrderDetail as D
inner join Orders as O on O.OrderID=D.OrderID where O.Shipped=0
and O.OrderDateOrders.OrderDate));

Okay - I am running into a little trouble. If I have 3 orders each one
has
order qty as an example
[quoted text clipped - 32 lines]

Stock

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.accessmonster.com