Select the best fitting carton

[scootswiss]

I am working on a process to select the best fitting carton for our product line. I have a table of all items item with the length, width, height and cube for each item in separate fields. I also have a table of our possible shipping cartons with the L, W, H and inside cube in separate fields. I would like Access to be able to use the item's dimensions to reference the carton table and select the carton where the Product L <= to all Lengths of all possible cartons in the carton table, Product W <= the all the Widths of all possible cartons carton, and the same for height. When the best carton is found it would update a field in the items table.

I.e. item size is 10 x 6 x 2, possible cartons are A = 8 x 5 x 3, B = 10 x 7 x 3 and C = 10 x 8 x 5. The process should select carton B (even though B & C will work) and update the field to that part number. Smallest cube would be the deciding factor in choosing carton B since all dimension criteria are met.

Items table has 1000's of items, carton table has 15 - 20 cartons.

 

[PHV]

What have you tried so far and where in your SQL code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scootswiss]

Here is my SQL statement so far;

UPDATE TblMultiProfile, TblGenCartonsForAnalysis SET TblMultiProfile.CtnB = [TblGenCartonsForAnalysis].[Ctn]
WHERE (((TblMultiProfile.L)<=[TblGenCartonsForAnalysis].[Lc]) AND ((TblMultiProfile.W)<=[TblGenCartonsForAnalysis].[Wc]) AND ((TblMultiProfile.H)<=[TblGenCartonsForAnalysis].[Hc]) AND ((TblGenCartonsForAnalysis.InsCube)=DMin("inscube","TblGenCartonsForAnalysis")));

 

[MajP]

I would think the best fitting box is found by the box with the smallest volume that meets all the minimum dimensions. Something like

SELECT 
 productID, 
 pLength, 
 pWidth, 
 pHeight, 
 cartonID, 
 cLength, 
 cWidth, 
 cHeight
FROM 
 tblCarton, 
 tblProduct
WHERE cartonID in (Select TOP 1 CartonID from tblCarton where cLength >= tblProduct.plength AND cWidth >= tblProduct.pWidth and cHeight >= tblProduct.pHeight order by clength * cwidth * cHeight)
ORDER BY productID;

 

[PHV]

Anyway, why storing a derived/calculated value you can easily retrieve in a query ?

SELECT M.*
,(SELECT Ctn FROM TblGenCartonsForAnalysis WHERE M.L<=LC AND M.W<=WC AND M.H<=HC AND InsCube=
 (SELECT Min(InsCube) FROM TblGenCartonsForAnalysis WHERE M.L<=LC AND M.W<=WC AND M.H<=HC)
 ) AS bestCtn
FROM TblMultiProfile M

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886