If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|