Hard problem - loop until?

[mbar]

Hi-

No idea how to solve this one....

I work for a distribution center and I'm trying to create code to calculate, given known dimensions of our products and qty's, the number of pallets needed to fill each order.

Here are the relevant fields:
skuid
percentpallet: the percentage of a pallet that an unit will utilize
unitqty: the total number of units.

for example, Order #1:
skuid, unitqty, percentpallet
A2128, 15, .05 (.75 PALLETS)
A2129, 10, .01 (.1 PALLETS)
A2130, 10, .12 (1.2 PALLETS)
A2131, 3, .05 (.15 PALLETS)

Does anyone know of a code that will provide this result:

Pallet1:
A2128 QTY 15
A2129 QTY 10
A2130 QTY 1
.97 PALLET

Pallet2
A2130 QTY 8
.96 PALLET

Pallet3:
A2130 QTY 1
A2131 QTY 3
.27 PALLET

or something similar?

basically need to loop until a variable <=1.00 If there's remaing qty for each sku, then it must be used for the next pallet

????

I have a good knowledge of access but little with VBA. PLEASE HELP!!!

 

[gemma-the-husky]

i don;t understand - can you explain again please

OK - i've reread it, and i do see now. What sort of structure are you trying to store your results in?

one point is - is it a problem that in your example, one of the products is being split over 3 pallets, when it could be split over 2?

 

[mbar]

Hi-

I probably want to display the results in a report.

As far as the other question, I suppose that each sku should be spread out over the least amount of pallets. In this case, 2 pallets

thanks for your reply

 

[gemma-the-husky]

Ok - the oversize pallets option is definitely going to complicate this

you can achieve it quite simply by processing the order as you suggested.

the problem is that, as in your example, a consignment that could be split into 2 pallets, gets split into 3, to accommodate the available space. The same will happen with a pallet that need not be split, but will be because of the algorithm used.

The alternative is to try and deal with all the pallets so that you get the minimum "split" pallets, but this will make the algorithm a lot harder.

I didn't mean a report itself - I take it this is a real world problem - and I meant what data tables do you use to store the pallet analysis.

Anyway, I will send you a PM

 

[mbar]

I'm not exactly sure what you mean when you ask 'what data tables' is used to store these? I haven't built this yet so I can store the data or save to a variable if needed and show on a report- that's what I meant earlier but I don't think this is what you were asking.

anyway, I'll look out for your PM

thanks again