Need Help Creating Query

[lit31686]

Hello all,

I need help creating a query and am lost as to where to begin.

I have a table with two fields: Product Name and Product Length in Inches. For example, Box Of Cereal and 12 inches, Can of Soup and 3 Inches, Box of Pasta and 5 inches, etc. My goal is to create a query that takes these products and creates a list of them based on the combination that will get me closest to a certain length (ie Total of 60 inches. What combination of products would be closest to 60). I wouldn't need every combination based on order as I would be placing them alphabetically.

Ideally, when I'm done, I would have a form where I could choose the different products I want to be included in the selection and the total length.

I don't know if I explained this all that well. If more information is needed, please let me know.

Thank you!

[RoryOF]

Other than the length, what other criteria for selection apply? Five boxes of cereal, or 20 cans of soup, or 12 boxes of pasta all fit your requirement.

[lit31686]

That's a good question. I would like the ability to select multiples units of each product (ie 3 boxes of cereal, 2 cans of soup, etc) or 0 of certain products. I guess I would need a form for this as well.

I'm just a little lost and some direction.

[RoryOF]

Will you specify certain quantities of key items, then expect the program to pad the selection to the 60 inches with other items? There must surely be some criteria for the core selection on each occasion. By the way, my spaghetti is 11 inches - I've just checked.

[lit31686]

I will specify the quantity of the items, but I don't want the query to pad with extra items.

I appreciate you checking. It seems as if I've grossly underestimated the length of the spaghetti haha

[UnklDonald418]

After looking at your problem I am wondering if there are a large number of possible products. If not a simple Calc worksheet might be a better option than Base.

[UnklDonald418]

It just occurred to me, you should look at this example
viewtopic.php?f=100&t=56006

At first blush it doesn't seem like an invoicing system would be applicable. But on closer examination it may be just what you are seeking.
Looking at the Invoice Basic example, instead of invoice number think shelf number, and instead of price think product length.