What is the best way to distribute a total batch weight into multiple reports? (1 Viewer)

cwats

Registered User.
Local time
Today, 05:18
Joined
Feb 20, 2019
Messages
40
Hey,



I am making batching instructions and need to have the ability to split a batch into smaller portions or vice versa.

What I am getting at is this,



I have a form to build the batches and decide what blender they will be mixed in. I want to be able to make a batch split depending on the weight of batch and what blenders are available.



Every blender has a weight capacity.

Blender 1 - 1200kg

blender 2 - 700kg

Blender3 - 250kg

Blender4 - 75kg



So if a batch has a total weight of 2400kg then i would like to split that into two 1200kg batches and choose Blender 1. this would put a limit of weight per batching/mixing instructions. the split would make identical batching instructions.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:18
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure this is correct but 2400/1200 = 2, so 2 batches for blender #1. Correct? If the total batch is 3100, then 3100/1200 = 2.583, so 2 batches for blender #1. Then, 3100 - 1200 * 2 = 700, then 700/700 = 1, so 1 batch for blender #2. And so on...
 

cwats

Registered User.
Local time
Today, 05:18
Joined
Feb 20, 2019
Messages
40
"Hi. Not sure this is correct but 2400/1200 = 2, so 2 batches for blender #1. Correct? If the total batch is 3100, then 3100/1200 = 2.583, so 2 batches for blender #1. Then, 3100 - 1200 * 2 = 700, then 700/700 = 1, so 1 batch for blender #2. And so on... "



theDBguy,



Yes, that is correct. It seems pretty simplistic but i can't figure it out to save my life! Would this have to be a vba code in the background or an expression using the exp builder?



thanks,

cwats
 

Mark_

Longboard on the internet
Local time
Today, 05:18
Joined
Sep 12, 2017
Messages
2,111
Can you write out the rules by which you break up the batch?
Also, does this need to check availability for each blender prior to assigning?

If you have a 700kg batch, do you want it assigned to Blender #2, available in 4 weeks, or would you rather have it broken down into 3 batches on Blender #3 which is available tomorrow? How do you want to handle when a batch is less than capacity?

This will get to be a fair amount of head scratching for you, especially since you are trying to write out what is common sense to you. Write it out as though it was instructions for a filing clerk who has no idea what a batch is. Then we should be able to help you replicate in code.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:18
Joined
May 21, 2018
Messages
8,463
It seems pretty simplistic
I do not know about that. This sounds like a variant of a Bin Packing Problem
https://en.wikipedia.org/wiki/Bin_packing_problem
Which if you are trying to be optimal, in complexity theory it is an NP hard problem.
So based on your rules this may be complex. You are likely going to have to code a non optimal heuristic to get an answer. So what are the rules. Use the least amount of blenders? Use the largest first? No unfilled blenders? No uneven batches?

For example what is the desired solution for 2875 or 2050. I have done a few optimization heuristics in VBA, but you need to define very clearly the rules in selecting. Also is this an example or is this the real number and types of blender. These are combinatoric problems. So the complexity goes up radically with the number of constraints.
 

cwats

Registered User.
Local time
Today, 05:18
Joined
Feb 20, 2019
Messages
40
Hey Everyone,



I have used this concept in an excel program that i am replacing with MS Access.



As you can see in the attachment the total weight(cellC45) is divided by the weight cap . in cell C66. if Cell C45 is zero then there would be no calculation and the cell is filled with 0.


If you look over at Cell P45 The formula, =IF(M45>$C$66,$C$66,M45) is used, this is where the batch splitting is calculated. Cell M45 is taking the total kilogram weight from the 'Top Level' sheet, converting it to lbs by *2.21, and calculating an error verification/ totaling the entire order not just individual batches.



This is what i am trying to replicate in access but i know that it has to be totally different that excel for smooth processing. I hope i made this all make a little more sense. let me know if you would like to see anymore info on this.







C66 View attachment Book1.zip
 

cwats

Registered User.
Local time
Today, 05:18
Joined
Feb 20, 2019
Messages
40
Just realized that the attachment might be locked,



if so, then the password is : wordup11



Thanks,



Cwats
 

Users who are viewing this thread

Top Bottom