Help with Expressions

  • Thread starter Thread starter LeLe
  • Start date Start date
L

LeLe

We inventory wood poles in 19' lengths which we then cut into smaller pieces
for our customers. Is there an expression I can use to help me reduce waste
when we are cutting the poles? At one time, we may be cutting 10 to 30
peices in differing lengths ranging from 18" to 160" or larger.
 
We inventory wood poles in 19' lengths which we then cut into smaller pieces
for our customers. Is there an expression I can use to help me reduce waste
when we are cutting the poles? At one time, we may be cutting 10 to 30
peices in differing lengths ranging from 18" to 160" or larger.
Click to expand...

This is an example of the NP-complete "Knapsack Problem", a notoriously
difficult class of problems to tackle mathematically. There is probably no
guaranteed way to find "the best" cutting sequence. I would suggest that the
best bet may be the "greedy algorithm" - start with the largest desired target
piece and cut it out of the smallest available stock piece; keep iterating
until you run out of stock or out of orders.
 
Try using these tables and queries. Query order to run is qryCheck,
qry_Fill, qryMark, and qryRe-Stock.
Stock --
StockID Size_Inches Used Old_StockID
1 228 Yes
2 120 Yes
3 144 No
4 150 Yes
5 3 No 1
6 20 No 2
7 50 No 4

Wood_Orders --
OrderID Size_Inches QTY Cut
1 50 2 Yes
2 75 3 Yes
3 150 1 Yes

Order_Fill --
Fill_ID OrderID Size QTY StockID
8 3 150 1 4
9 2 75 3 1
10 1 50 2 2
qryCheck --
SELECT [Stock].[Size_Inches]-([Wood_Orders].[Size_Inches]*[QTY]) AS Tret,
[Wood_Orders].[Size_Inches]*[QTY] AS Total_Cut, Wood_Orders.Size_Inches,
Wood_Orders.QTY, Stock.StockID, Stock.Size_Inches, Wood_Orders.OrderID,
Wood_Orders.Cut, Stock.Used
FROM Wood_Orders, Stock
WHERE ((([Wood_Orders].[Size_Inches]*[QTY])<=[Stock].[Size_Inches]))
ORDER BY [Stock].[Size_Inches]-([Wood_Orders].[Size_Inches]*[QTY]),
[Wood_Orders].[Size_Inches]*[QTY] DESC , Wood_Orders.QTY DESC;

qry_Fill --
INSERT INTO Order_Fill ( OrderID, [Size], QTY, StockID )
SELECT qryCheck.OrderID, [Wood_Orders.Size_Inches] AS [Size], qryCheck.QTY,
qryCheck.StockID
FROM qryCheck
ORDER BY qryCheck.Tret;

qryMark --
UPDATE Stock INNER JOIN (Wood_Orders INNER JOIN Order_Fill ON
Wood_Orders.OrderID = Order_Fill.OrderID) ON Stock.StockID =
Order_Fill.StockID SET Stock.Used = -1, Wood_Orders.Cut = -1
WHERE (((Stock.Used)=0) AND ((Wood_Orders.Cut)=0));

qryRe-Stock --
INSERT INTO Stock ( Size_Inches, Old_StockID )
SELECT qryCheck.Tret, qryCheck.StockID
FROM qryCheck
WHERE (((qryCheck.Tret)>0) AND ((qryCheck.Used)=-1));
 
??
John W. Vinson said:
This is an example of the NP-complete "Knapsack Problem", a notoriously
difficult class of problems to tackle mathematically. There is probably no
guaranteed way to find "the best" cutting sequence. I would suggest that
the
best bet may be the "greedy algorithm" - start with the largest desired
target
piece and cut it out of the smallest available stock piece; keep iterating
until you run out of stock or out of orders.
Click to expand...
 
Back
Top