allocating products into boxes

Hello, I am new to this, and very stuck on a query problem.



In my database, I am looking at allocating products/line items on a packingslip into shipping boxes.
The max volume per box is 400, the individual line items are in multiples of 100.
It should 'build' the full boxes of a single item first, and 'build' the partial boxes last.
If the item quantity is less than 400, it should put multiple items per box.
It should look something like this:

• Quantity Box#
• Item1 600 1 & 5
• Item2 600 2 & 5
• Item3 600 3 & 6
• Item4 600 4 & 6

Next, I need a box content output something like this:

• Box 1 Item1 Quantity 400
• Box 2 Item2 Quantity 400
• Box 3 Item3 Quantity 400
• Box 4 Item4 Quantity 400
• Box 5 Item1 Quantity 200
• Item2 Quantity 200
• Box 6 Item3 Quantity 200

Thanks in advance for any help

Options:

1. manual calculation and data entry

2. lots of VBA coding.

So......is there no other way other than lots of VBA code
I would like to stay away from that

Records need to be created. Automating the breakdown logic as you describe and creating the records is not possible with query. Would be complicated VBA.

I am assuming this has been done before, and do not want to re-invent the wheel.
Any idea where I can find some code to get me started?

No idea at all.

I'm sure this has been done before too, but I think you have added some constraints by naming
the Boxes to contain the specific items, and by having the same number of each item type.
You may have made the issue more complex.

As June said, a solution would probably require lots of vba for expressions and logic.
I expect the more general problem is one where you have various quantities of items of
various sizes, and a number of available boxes with various capacities, and you would be
asked to minimize the number of boxes used.... more of an operations research thing.

This may be useful

If you have N units of ItemX, and you are using boxes with Capacity of C units of ItemX,
then you can determine how many boxes you need.

TotalBoxes = Iif( N mod C > 0,N/C +1, N/C) where / is integer divide

You would need logic to determine which particular boxes (Box1 and 5) should be filled
with which items (Item1)

Good luck.

Thank You, that helps a lot.
Would it be easier to not name the boxes? Essentially it would come down to the same number of boxes.
Would you suggest to store the info in a table, or run it in a query?

How would you go about keeping a running total of the boxes as they are filled up?

Thanks again.

Just typing as I'm thinking so not tested.

You need to know the quantity of each type of Item; and you need to know the capacity of a box in terms of the number of Items it can contain.
Depending on the quantity, you assign the full box capacity number of Items, then identify that Box as full, and reduce the number of remaining items by the quantity you put in the box.

If the quantity of Item remaining is less than the box capacity, then you put that quantity in a box. Now, you would have to check if there is an unfilled box (eg is there a partially filled box available) if so, thenwhat available capacity exists for that box. If that Capacity is => than the quantity of items remaining, then put those items in the box ; then reduce the quantity of Items by the amount put in the box and reduce the available, remaining capacity of the box.

Hope you can make some sense of it.

I would have a table or list of Items and quantity; and a list of boxes.

Once you put the Items in the box, and there is no more available capacity, you can mark that box as Full (and Processed)
If there is available capacity in the box, you record the available capacity in that box and record it such that you can fill it up if you have another partial quantity.
You fill full boxes first, then fill those that are partially filled.
Good luck.

Thank You so much for thinking this through, that is exactly what I need to do, but cannot grasp how to apply this into my Database.

I have all the numbers I need, i.e. quantity per item (which varies anywhere from 100 to 1000), box capacity (Which is a constant 400)

Any help you can give me on how to proceed is much appreciated.

PMFJI,

Using what Orange suggested, I would suggest getting a pencil and a tablet and manually go through the steps, writing them down as you go. This is called pseudo code. Then go through the steps you wrote down and refine them. And maybe a 3rd and 4th time.

Once you have the process working manually, start the coding. Each step in the pseudo code might (will) convert to many code lines.
An example would be
Pseudo code:

Code:

Open a recordset to get the stock on hand

The pseudo code would become:

Code:

Dim d as DAO.Datebase
Dim r as DAO.Recordset
Dim sSQL as string

Set d = Currentdb

sSQL = "SELECT blah, blah"
Set r = d.Openrecordset (sSQL)
<More lines of code>

So if your pseudo code on paper is 2 pages, the actual code might end up 8 pages.

I used this process and ended up with around 50 letter sized pages for one button click. The code keeps growing because I have to add validation; the data is not always clean.

Happy coding ...

Pacific1,

Attached is a database with some sample data and function to Allocate Items from tblItem to boxes in tblUsedBoxes.

Orange,
This is GREAT!!! works like a charm, it is exactly what I have been struggling with for days on end without any success.

Thank You so much for your help

Happy to help.
Good luck with your project.
Post back if you need something.

Orange, I think I have to call on you again............
My next issue....
I am running the code you created from my purchase order screen to do the box distribution for that particular order. But, it is trying to box everything in the transaction table rather than the current order. I need to filter to the current TransactionID.

Any suggestions?