Optimum Batch Size Selection

[nsagar]

Dear All
I need to create a solution for following :
1. I have a Product Master Table in which, I have fields like ProductID, Product Name etc.
2. I have created another table for Batch Size (One to Many relationship) as my one ProductID can have multiple batch size. (But these are standard batch sizes on which the number of batches to produced to be calculated)
3. I have Demand Table in which I have ProductID and the Market Demand (along with Demand Month/Date etc.)
The situation is as follows: A Product "X" can have 3 standard batch size (in BatchSize Master) e.g.
(i) 100000, (ii) 500000 and (iii) 1000000 nos.
Suppose my demand is
a) from 1 to 400000 nos. I want, system should propose to make as many number of batches of 100000 each (depending on nearest figure)
b) If the demand is between 400000 to 500000, the system can propose to make one batch of 500000 units.
c) If my demand is 600000 units, system should propose one batch of 500000 units and 2nd batch of 100000 units.
d) Similarly, If the demand is 700000 units, system can propose to make one batch of 500000 units and 2 batches of 100000 units.
e) If demand is between 900001 to 1000000 units, system can propose to make 1 batches of 1000000 units.
and so on and so forth.
Like wise Product "Y" can have altogether different batch size in BatchSize Master.

I need your support to resolve this.
Please advise..


Thanks

[ItsMe]

The way I see it, your table for Batch Size should have three records in it, 100000, 400000, 1000000. Then you would have functions determine which size and how many to use by referencing the table.

It probably makes more sense to include those values in your VBA code as Enums. Then within the same Standard Module, you could create various functions that would compare the nos Demand to the available Batch Sizes. I am imagining a couple of private functions that support a single public function. Provide the Demand to the Public function and have the Public function use the helper methods to return an answer.

So, as the User interacts with a form, code behind the form can pass the Demand argument to the Public Function. When the public function returns an answer, code behind the form will manage the appropriate updates, appends, etc.

[nsagar]

The way I see it, your table for Batch Size should have three records in it, 100000, 400000, 1000000. Then you would have functions determine which size and how many to use by referencing the table.

It probably makes more sense to include those values in your VBA code as Enums. Then within the same Standard Module, you could create various functions that would compare the nos Demand to the available Batch Sizes. I am imagining a couple of private functions that support a single public function. Provide the Demand to the Public function and have the Public function use the helper methods to return an answer.

So, as the User interacts with a form, code behind the form can pass the Demand argument to the Public Function. When the public function returns an answer, code behind the form will manage the appropriate updates, appends, etc.

Hi..
Thanks for the response.. I think that precisely I'm looking for..
however I'm wondering how to make it happen.. I'm totally novice as far as VBA coding is concern.
Please help me move further, this is very important for my project.
Thanks

[ItsMe]

You may have an idea in your head of the business process that defines the need. You will need to define this as an abstract idea. You will need to visit and revisit the abstract as you engineer the structure for the data.

Perhaps you can revisit post #2 and consider post #2 as you define the Business Rule, specifically from the User's perspective. For instance, the User needs to process a nos request of 70000. The User needs an answer to which batch sizes and how many of each batch size is required to process the request. This scenario will produce the need of a total batch number of three, two of one size and one of another size. In this scenario, there is not a remainder of nos demand. Sometimes there may be a remainder. Remainders create a situation that needs to be understood as a remainder and the remainder needs to be defined as its own entity.

With this business rule. You can begin to define an Entity Relational Diagram (ERD). You will need an ERD before getting into what might result in some fancy VBA. You won't realize what is necessary until after you start playing around with it.

[nsagar]

Yeah..!! you are right. I have to make some business rules.. (Although, I'm not able to understand your all the points (specially about ERD!!, but I think I understood the bottom line).
Can you please suggest me what should be my next step.
Thanks

[ItsMe]

I am unsure what you have done so far. It will help if you view Access as two things. On one side you have an RDBMS. On the other side, you have an application development environment. Access is considered to be a Rapid Application Development (RAD) tool. It is an RAD because it has many tools and wizards that quickly generate graphical user interfaces.

So, on the RDBMS you have the database and the system that provides you, the database engineer and the database administrator, with the tools to architect, develop, and manage the database. The ERD is a diagram that defines the entities. A single entity will typically be defined by a single table. Because we are modeling for a Relational Database, we will also expect our ERD to illustrate how the various entities relate to each other.

I would use blank copy paper and a pencil to begin sketching out the various iterations of your ERD. There will be many revisions as you consider the Business Rules and discover new Business Rules, and work towards getting your data in Third Normal Form.

Make sure that you take information you get on the web with a grain of salt. You will always see differences in opinion about what is correct information and what best practices are. Having said that, here are three videos that I can recommend for people breaking into RDBMS.

This video might help to provide a very high level view.
https://www.youtube.com/watch?v=-fQ-bRllhXc

You want to have an understanding about Keys. For the most part, you will want to understand Primary and Foreign Keys. However, it does not hurt to understand Business and Candidate Keys, too.
https://www.youtube.com/watch?v=_aN-8kszIdA

Normalizing your data is a big issue. Here is a video that hits on Normalizing data.
https://www.youtube.com/watch?v=y_MDbbqQIUU