Back Order Fill

[Guest]

Hi:

I am attempting to create a Back Order Fill Query.

I have a table of back orders containing SKU, Customer, Invoice Number, Sale
Date, Time and qty ordered.

I want to look at the inventory file and determine which orders can be
filled, the sequence is by sale date and time (first come, first served).

I'm at a loss on how to view/match to the inventory file but only print
those for which there is enough inventory to fill and leave the others on
back order. If I link the two tables together the OnHand Qty, and On Order
qty is repeated for each sku found in the sales table.

Example:

Sales Table Inventory Table
SKU=123 SKU=123
OrdQty= 4 OnHand=3
OrdNum: 2001 OnOrder=10
Orddate: 7/21/2006
OrdTime:11:56

SKU=123
Ordqty=1
OrdNum: 2002
OrdDate: 7/21/2006
OrdTime:12:23

3 of SKU 123 need to go to order 2001, leaving 1 on backorder. Order 2002
remains back ordered.

The query should only display order 2001 for 3, leaving 1 on back order.

I hope I'm explaining this properly.

Any ideas - help please!

Thank you!

Deb

 

[Michel Walsh]

Hi,


A running sum of the quantity:


SELECT a.OrdNum,
a.SKU,
LAST(a.OrdQty) As QtyThisOrdRequires,
SUM(b.OrdQty) As SumOfPreviousOrdQty

FROM myTable As a LEFT JOIN myTable As b
ON a.SKU = b.SKU AND a.OrdNum > b.OrdNum

GROUP BY a.SKU, a.OrdNum

HAVING Nz(SUM(b.OrdQty),0) <= ( SELECT QtyOnHand
FROM inventory
As c
WHERE
c.SKU=a.SKU)



Note that I use the ordering based on OrdNum, not on the date and time of
the order.
It also list all OrdNum that can be filled, NOT JUST the FIRST ONE that
cannot.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Michael,

Thank you. I'm afraid this may be a little over my head.

If I understand correctly:

Create a select query(query1) that pulls SKU, OrdNum, OrdQty - group by SKU
OrdNum, Last OrdQty as QtyReq, then SUM OrdQty as PrevOrdQty. (table
name=tblOrders)



Create another select query (query2) using tblOrders and above query - left
join to tblOrders (join on SKU and OrdNum) - create a field to identify where
Query1.ordnum > tblOrder.ordnum. Group by SKU, Query1.OrdNum.

and I don't understand the Having Nz(SUM(b.OrdQty),0) <= Select QtyOnHand
FROM Inventory where ........

Thank you again for your help!

Deb

 

[Michel Walsh]

HI,



See "a" and "b" as two tables, even if they, indeed, refer to the same
table. Then, for each record in table "a", we keep, for considerations, the
records, in table "b", that have the same SKU and that have a lower number
of OrderNumber:

ON a.SKU = b.SKU AND a.OrdNum > b.OrdNum


so, effectively, SUM(b.OrdQty) will then sum over these records we kept,
giving, in English, the sum of qty proceeding the actual record in table
"a".

Since we used SUM, we need to use a GROUP BY. Clearly we want the SUM to
occur by SKU, and grouping by order number sounds appropriate too, given the
nature of the problem. We could have added a.OrdQty too, in the GROUP BY,
but that is not really a group we want, so, I opted to use aggregate it,
instead. Just a matter of syntax, a case where the syntax just help to
remember the logic going on: a value in the SELECT must: -- either be in
the GROUP BY, --either be aggregated, -- either be an arithmetic
expression with elements from the first two cases.


Now, the HAVING clause is a criteria occurring AFTER the SUM did occur. We
only want to keep, in the result, the records for which the SUM we just get
is less or equal to the quantity on hand we have, for the given SKU. That
was a requirement given by the initial problem.

Since the SUM(qty) occurred on order preceding the record in consideration
in table "a", and not the order in consideration ITSELF, since we used >,
not >=, the sum is, indeed, limited to orders strictly preceding the record
in consideration. This is as stated in the problem. Now, for the first time
we meet a given value of a given SKU, there is no such "preceding" record
(because it is the first one, sadly), so, SUM(b.qty) will result into a
NULL value (thanks to the outer left join, an inner join would have exclude
the record from table "a", in this particular case, would be really really
sad, so, indeed, we NEED the outer join). The logic tell us that this NULL
value, is, in reality, a zero (0 = quantity ordered before is the SUM of
"previous" orders, in that case), so, we coalesce that null to a zero, in
cases such a null shows its ugly nose.

That is basically it.


Hoping it may help,
Vanderghast, Access MVP