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

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

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

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

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.

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

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

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

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

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