Query that provides combination of data that meet a certain criteria

This is the brute force solution that may not perform the best, but the one I can think of from the top of my head:

DECLARE @t TABLE (
        ID INT identity(1, 1) PRIMARY KEY
        ,[Length] INT
        ,[Width] INT
        ,Pieces INT
        )

INSERT INTO @t (
        [Length]
        ,[Width]
        ,Pieces
        )
VALUES (
        300
        ,600
        ,20
        )
        ,(
        600
        ,400
        ,10
        )
        ,(
        900
        ,600
        ,5
        )

DECLARE @LengthNeeded INT = 900
        ,@WidthNeeded INT = 1200;

SELECT T.[Length]
        ,T.Width
        ,L.X * W.Y AS Pieces
FROM @t T
CROSS APPLY (
        SELECT @LengthNeeded / T1.[Length] AS X
        FROM @t T1
        WHERE T1.ID = T.ID
                AND (@LengthNeeded * 1.0) / T1.[Length] = CAST((@LengthNeeded * 1.0) / T1.[Length] AS INT)
        ) L
CROSS APPLY (
        SELECT @WidthNeeded / T1.[Width] AS Y
        FROM @t T1
        WHERE T1.ID = T.ID
                AND (@WidthNeeded * 1.0) / T1.[Width] = CAST((@WidthNeeded * 1.0) / T1.[Width] AS INT)
        ) W
WHERE L.X * W.Y <= T.Pieces;

You can change the above CROSS APPLY into INNER JOIN using the same idea and I think in that way the query will work in Access.

Unfortunately, as I just realized, this query will not work for combinations (option 3 in your case). To produce combinations we need more complex query - I think I need to read about knapsack problem first myself to figure this out.

I guess with recursive cte we may figure out the combinations problem, but recursive cte is not available in Access anyway.

---

For every expert, there is an equal and opposite expert. - Becker's Law





My blog



My TechNet articles