# Calculating reorder quantities

Windows 10 Access 2016
Join Date
May 2019
Posts
89

## Calculating reorder quantities

I am stalling with this as it may require some VBA I am not too familiar with unless there is another easier way.

Here's what I have so far:

Code:
 Component_ID MOQ Multi Planned_Date Stock Supply Demand Balance Order Qty LBalance Expr2 Expr1 item1 10000 10000 16-Aug-19 0 44000 0 44000 0 item1 10000 10000 21-Aug-19 0 0 43554 446 0 item1 10000 10000 01-Sep-19 0 0 31110 -30664 40000 9336 0.934 0 item1 10000 10000 01-Sep-19 0 0 518.5 -31182 40000 8818 0.882 0

The [order qty] in line 3 is correct, this is a previously calculated field inserted in a table this query is pulling from.
The [order qty] in line 4 is incorrect, this calculation is made line by line but is only correct on the first line, L3 in that example.

Expr1 is a calculated field in that query, here's the formula

Code:
Expr1: IIf((([Balance]+[Order Qty])/[MOQ])<0,[MOQ]+(-Int(-((Abs([Balance]+[Order Qty])-[MOQ])/[MOQ]))*[Multi]), IIf(((Abs([Balance]+[Order Qty]))/[MOQ])=1,[MOQ],"0"))

LBalance and Expr2 are also calculated but is not necessary, it's just for ease of building at this time then I'll discard them

Expr1 represents what should be reordered, as soon as the new balance [LBalance] becomes negative again. Expr2 shows the ratio of stock vs MOQ or Multi.
When that ratio is <0, the formula will calculate what needs to be ordered. If, on line 4, the balance LBalance was -15,000 for example, Expr1 would show 20,000 to be reordered which is Roundup(15,000/10000)*10000

This is working fine but it's confusing because it is showing information that is not needed or is incorrect. [order qty] = 40,000 L4 is irrelevant and incorrect.

What I would like to have is a column that would show

L1 = 0 balance is 44,000 so nothing to order
L2 = 0 balance is 446 so nothing to order
L3 = 3 balance is -30664 so we are ordering 10,000(MOQ) + 3x 10,000(Multi) = 40,000
L4 = 0 balance is 8818 so nothing to order
L5 (not shown) = 10,000 balance is -3000 so we are ordering 10,000(MOQ)

That information eventually needs to go on a report and lines where [balance]>0 and LBalance>0 will be filtered out, I just want to show lines when stock becomes negative and the suggested order quantity.

Thank you

2. You need to calculate quantity on hand then compare that to the desired supply and order the difference.

For calculating quantity on hand, review http://allenbrowne.com/AppInventory.html

Windows 10 Access 2016
Join Date
May 2019
Posts
89
Qty on hand is being calculated as [LBalance] when [Balance]<0. Qty to order is being calculated as Expr1 (I'll rename that eventually).

That works, except for the very first negative balance line, the qty to order is stored [Order Qty] instead of [Expr1]

So what I'm trying to do is extract the first record of [Order Qty] (for each item), store it, then extract records from Expr1 where Expr1>0 and dump these in a new column.

So it would create a column like this

L1 <empty>
L2 <empty>
L3 <44,000> = First value of [Order Qty] And Where [Expr1] = 0
L4 <empty>
L5 <20,000> = next value from [Expr1] where [Expr1]>0

4. Sorry, I am lost. Why would you need to show multiple lines for same item? Why not just calculate the aggregate data for each Component_ID? Why would line 4 not be correct? Why would you pull line 3 and not line 4? Why are there 2 entries for 01-Sept?

Saving calculated aggregate data is usually a bad idea (reasons and exceptions in referenced link)

Perhaps a nested TOP 1 query would serve. Use the query you posted as source. http://allenbrowne.com/subquery-01.html#TopN

5. Many people will set a reorder point ( when stock drops to or below X), then time to reorder. And when they reorder, they Order W units(ReOrderQty).

You may get some ideas from this free Stock Management video from SoftwareMatters.

Windows 10 Access 2016
Join Date
May 2019
Posts
89
Originally Posted by June7
Sorry, I am lost. Why would you need to show multiple lines for same item?
It's not that I "need" to, it's how the data comes. Each line corresponds to 1 "operation" at a specific date and that date is important.

For example if there is stock, I'll have 1 line with a value in the stock col. and nothing in the other columns.
Then if there is a demand at date X, there will be a line with the demand for that specific date.
Then another demand at an other date (or same date) will create a new line and so on.

The same goes with the supply col. An incoming PO with an ETA at date X will create 1 line on its own and so on.

So each supply or demand will create 1 line. Stock creates only a single line.

Line 3 is telling me that on 9/1 we are 30,664 units negative. It calculates that based on MOQ and Multi, I need to order 40,000 units. Then I get a balance of 9,336 pcs.

Line 4 is telling me I have subsequent demand on 9/1 for 31,182 units. The issue I have is creating a calculation, that will use the balance of the previous line to calculate what I need to order.

The calculation would then re-calculate the true demand: -31,182 + 9,336 = 21,846 and the Order Qty would be 30,000 and not 40,000, with a new balance of 30,000-21846 = 8,154 instead of 8,818

Windows 10 Access 2016
Join Date
May 2019
Posts
89
Originally Posted by orange
Many people will set a reorder point ( when stock drops to or below X), then time to reorder. And when they reorder, they Order W units(ReOrderQty).

You may get some ideas from this free Stock Management video from SoftwareMatters.
The reorder point is when the stock balance becomes <0. What I have not managed to do is calculate the order based on the current line demand and the previous line balance.

8. Not everyone decides to reOrder after they run out of product.
Reorder is often when qty of Product on Hand reaches some positive number like 10 or 20 or, based on recent demand some qty representing 1 or 2 weeks supply or similar. Then, they order the predefined reOrderQty.

Windows 10 Access 2016
Join Date
May 2019
Posts
89
Right, we reorder when product on hand reaches 0 or less but at a future date not when it actually reaches 0.

So we order 1 to 3 months in advance based on production forecasts, orders, current stock level and lead times. That is why the dates here are important and why each date will create its own line. That way I can see we may have a need for 9/1 and maybe down the road we have a need for 9/15, will order for delivery 8/25 then 9/10 and so on.

The cascading lines as shown in my example give me that sequential information.

All I'm trying to do then is calculate what to order based on that cascading information (taking in account the previous line information), currently I do it manually, it works but I'm trying to make life easier.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Tech Forums: Microsoft Office Forums