FIFO Help

[mattc66 via AccessMonster.com]

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

 

[Graham Mandeno]

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.Qty<OrderDetail.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;

 

[mattc66 via AccessMonster.com]

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?

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.Qty<OrderDetail.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

 

[mattc66 via AccessMonster.com]

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?

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.Qty<OrderDetail.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

 

[Piet Linden]

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.

 

[mattc66 via AccessMonster.com]

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?

 

[Graham Mandeno]

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

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?

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.Qty<OrderDetail.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

 

[Graham Mandeno]

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.OrderDate<Orders.OrderDate));

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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?

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.Qty<OrderDetail.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

 

[mattc66 via AccessMonster.com]

Yes I do have a table of open purchase orders.

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]

 

[mattc66 via AccessMonster.com]

How do you add a sub query?

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.OrderDate<Orders.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]

 

[Graham Mandeno]

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

Graham Mandeno [Access MVP]
Auckland, New Zealand

How do you add a sub query?

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.OrderDate<Orders.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