Simple re-order point model

[JessPedersenSCM]

Hi all,




First of all - great site. I have been reading through the forum for quite some time and have found many threads quite valuable. But it is time to create a thread for my own since I have not been able to find a discussion for my question.


I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.


I have one table with the following field and data:


ItemId....Red....Yellow....Green....Multiple....In ventory position
0001.... 10.... 30.... 50.... 5.... 45
0002.... 5.... 40.... 47.... 5.... 23
0003.... 11.... 20.... 30.... 10.... 5


I would like to generate new records (in another table) based on the above fields and three records.


Basically the end result should look as the following:


ItemId....Qty....Start inv....Aggregated inventory....Prioritization
0002.... 5.... 23.... 28.... Yellow
0002.... 5.... 28.... 33.... Yellow
0002.... 5.... 33.... 38.... Yellow
0002.... 5.... 38.... 43.... Green
0002.... 5.... 43.... 48.... Green
0003.... 10.... 5.... 15.... Red
0003.... 10.... 15.... 25.... Yellow
0003.... 10.... 25.... 35.... Green


The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.


The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.


It would be much valuable if someone could guide me in the right direction towards an appropriate VBA design. Alternatively some kind of loop query design (but I believe that VBA is required to make the above - particularly on the loops and running sum).


I am looking forward some great answers.


Much appreciated - thank you in advance.


//Jess

[burrina]

If I understand correctly what you are really wanting to see is the amount that needs to be ReOrdered! If this is the case, why populate another table?

[JessPedersenSCM]

Burrina:

Thanks for the quick reply and good question :-)

The reason for population another table is to split the aggregated reorder amount in multiples. If you think of the situation in which a quantity of 100 in total is required to be reordered but the multiple is 50 - that means two lines need to be created. In our case this will appear as two separate purchase order lines against the suppliers which ultimately could have two different priorities.

All these lines from table two will be forwarded to our suppliers as separate reorder quantities with individual priorities against our inventory stock position.

Therefore the table 2 is more an output table (it could be called purchase order lines with priorities) whereas the table 1 is the actual table which holds the original information.

I hope that clarifies the reason for populating another table :-)

[burrina]

Normally those aggregated/calculated values are done using a report with a query record source. Since the raw data is already there, I would think this a better approach! This of course could be displayed on a form as well as a report. Hard to advise without knowing more of your db structure.
1. Is this being done a main form subform/datasheet?
2. Table 2 is simply duplicated data, only calculated.
3. How are you displaying your data right now?

[JessPedersenSCM]

I would argue that the data is not there - at least the data is aggregated :-) But that is another discussion.

The main thing is that the table 2 is to be used for external sharing with vendors on vendor portal etc. This is critical that it is in a structured relational format and not in a report.

It is actually a quite simple data structure - table 2 is to be created from table 1 through VBA code :-)

I am just lost on how to actually do this operation - therefore I would really like some help on this part.

Thanks!

[burrina]

Appending data to another is a simple process but splitting it is not. However your post has been viewed 52 times, so not so simple! June7 should be along shortly.
Perhaps a Demo would help.
Good Luck with your project!