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 |
#1
|
|||
|
|||
FIFO Help
Hi All,
I am working on a scheduling program. We need to schedule shipments of orders and pull from stock. The orders would be in a FIFO method or First In First Out basis. The first order in for an item, if the item is in stock that order will ship. The thing I am struggling with is orders with multiple items and the order must ship complete. Example: Order#1 OrderDetail Qty Item 2 Widget1 1 Widget4 Order#2 OrderDetail Qty Item 1 Widget1 1 Widget4 Inventory Available Qty Item 1 Widget1 1 Widget4 We don't have enough inventory to fill Order#1, but we can fill Order#2. Even though order#1 came in first we would fill Order#2 rather then hold up the order#2. Does anyone know how I could show in a query or multiple query's this selection? Stock -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200809/1 |
#2
|
|||
|
|||
FIFO Help
Hi Matt
I would create a query from OrderDetail and Inventory showing which OrderDetail records have insufficient stock: qryInsufficientStock =============== Select OrderDetail.OrderID, OrderDetail.ProductID from OrderDetails inner join Inventory on OrderDetails.ProductID=Inventory.ProductID where (Inventory.QtyOrderDetail.Qty); Then use the existence of a record in this query to eliminate orders that are able to be processed: Select * from Orders where not exists (Select OrderID from qryInsufficientStock as X where X.OrderID = Orders.OrderID) order by OrderDate; -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "mattc66 via AccessMonster.com" u16013@uwe wrote in message news:89b51666bbd04@uwe... Hi All, I am working on a scheduling program. We need to schedule shipments of orders and pull from stock. The orders would be in a FIFO method or First In First Out basis. The first order in for an item, if the item is in stock that order will ship. The thing I am struggling with is orders with multiple items and the order must ship complete. Example: Order#1 OrderDetail Qty Item 2 Widget1 1 Widget4 Order#2 OrderDetail Qty Item 1 Widget1 1 Widget4 Inventory Available Qty Item 1 Widget1 1 Widget4 We don't have enough inventory to fill Order#1, but we can fill Order#2. Even though order#1 came in first we would fill Order#2 rather then hold up the order#2. Does anyone know how I could show in a query or multiple query's this selection? Stock -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200809/1 |
#3
|
|||
|
|||
FIFO Help
This looks like your suggestion is working. The next step would be to include
the Qty Due and Due date from the open purchases order for those order that can ship so we can determine the estimated date that the order can ship. Any ideas? Graham Mandeno wrote: Hi Matt I would create a query from OrderDetail and Inventory showing which OrderDetail records have insufficient stock: qryInsufficientStock =============== Select OrderDetail.OrderID, OrderDetail.ProductID from OrderDetails inner join Inventory on OrderDetails.ProductID=Inventory.ProductID where (Inventory.QtyOrderDetail.Qty); Then use the existence of a record in this query to eliminate orders that are able to be processed: Select * from Orders where not exists (Select OrderID from qryInsufficientStock as X where X.OrderID = Orders.OrderID) order by OrderDate; Hi All, [quoted text clipped - 33 lines] Stock -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200809/1 |
#4
|
|||
|
|||
FIFO Help
Okay - I am running into a little trouble. If I have 3 orders each one has
order qty as an example Order 1 item1 qty 2 Order 2 item1 qty 1 Order 3 item1 qty 3 Inventory stock of item1 = 5 the query will report that it can ship all these orders. However it can only ship 2 of the 3 orders. Any ideas how I can over come this issue? Graham Mandeno wrote: Hi Matt I would create a query from OrderDetail and Inventory showing which OrderDetail records have insufficient stock: qryInsufficientStock =============== Select OrderDetail.OrderID, OrderDetail.ProductID from OrderDetails inner join Inventory on OrderDetails.ProductID=Inventory.ProductID where (Inventory.QtyOrderDetail.Qty); Then use the existence of a record in this query to eliminate orders that are able to be processed: Select * from Orders where not exists (Select OrderID from qryInsufficientStock as X where X.OrderID = Orders.OrderID) order by OrderDate; Hi All, [quoted text clipped - 33 lines] Stock -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200809/1 |
#5
|
|||
|
|||
FIFO Help
You would have to process each line item (ProductID, Quantity)
individually. The reason you're getting that result is that each *individual* quantity is less than or equal to your quantity on hand, but the *total* is not. If you process the requests by using a recordset, you can loop through the open orders and compare to a quantity on hand, which will change... but you can't do it directly in a query, because you cannot show the QOH decreasing after each order. |
#6
|
|||
|
|||
FIFO Help
I agree - I was thinking that something like this would have to be done. My
thought would be to hit a button and have the order detail run through and validate the order as shipable or not. Then store it in a temp file. How would I write the code to loop though the records? Piet Linden wrote: You would have to process each line item (ProductID, Quantity) individually. The reason you're getting that result is that each *individual* quantity is less than or equal to your quantity on hand, but the *total* is not. If you process the requests by using a recordset, you can loop through the open orders and compare to a quantity on hand, which will change... but you can't do it directly in a query, because you cannot show the QOH decreasing after each order. -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200809/1 |
#7
|
|||
|
|||
FIFO Help
Hi Matt
Unless I'm misunderstanding you, it would be necessary to tap into your inward purchases table to get the expected delivery date of your new shipment of widgets that will satisfy the back order. Do you have such a table? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "mattc66 via AccessMonster.com" u16013@uwe wrote in message news:89b88ac4552b0@uwe... This looks like your suggestion is working. The next step would be to include the Qty Due and Due date from the open purchases order for those order that can ship so we can determine the estimated date that the order can ship. Any ideas? Graham Mandeno wrote: Hi Matt I would create a query from OrderDetail and Inventory showing which OrderDetail records have insufficient stock: qryInsufficientStock =============== Select OrderDetail.OrderID, OrderDetail.ProductID from OrderDetails inner join Inventory on OrderDetails.ProductID=Inventory.ProductID where (Inventory.QtyOrderDetail.Qty); Then use the existence of a record in this query to eliminate orders that are able to be processed: Select * from Orders where not exists (Select OrderID from qryInsufficientStock as X where X.OrderID = Orders.OrderID) order by OrderDate; Hi All, [quoted text clipped - 33 lines] Stock -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200809/1 |
#8
|
|||
|
|||
FIFO Help
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)); -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "mattc66 via AccessMonster.com" u16013@uwe wrote in message news:89b8f4f86f526@uwe... Okay - I am running into a little trouble. If I have 3 orders each one has order qty as an example Order 1 item1 qty 2 Order 2 item1 qty 1 Order 3 item1 qty 3 Inventory stock of item1 = 5 the query will report that it can ship all these orders. However it can only ship 2 of the 3 orders. Any ideas how I can over come this issue? Graham Mandeno wrote: Hi Matt I would create a query from OrderDetail and Inventory showing which OrderDetail records have insufficient stock: qryInsufficientStock =============== Select OrderDetail.OrderID, OrderDetail.ProductID from OrderDetails inner join Inventory on OrderDetails.ProductID=Inventory.ProductID where (Inventory.QtyOrderDetail.Qty); Then use the existence of a record in this query to eliminate orders that are able to be processed: Select * from Orders where not exists (Select OrderID from qryInsufficientStock as X where X.OrderID = Orders.OrderID) order by OrderDate; Hi All, [quoted text clipped - 33 lines] Stock -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200809/1 |
#9
|
|||
|
|||
FIFO Help
Yes I do have a table of open purchase orders.
Graham Mandeno wrote: Hi Matt Unless I'm misunderstanding you, it would be necessary to tap into your inward purchases table to get the expected delivery date of your new shipment of widgets that will satisfy the back order. Do you have such a table? This looks like your suggestion is working. The next step would be to include [quoted text clipped - 27 lines] Stock -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200809/1 |
#10
|
|||
|
|||
FIFO Help
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 | |
|
|