Results 1 to 9 of 9

Calculating reorder quantities

  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107

    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. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,363
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    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. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,363
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,334
    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.

  6. #6
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Quote Originally Posted by June7 View Post
    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

  7. #7
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Quote Originally Posted by orange View Post
    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. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,334
    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.

  9. #9
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating Quantities in a Query?
    By chohan78 in forum Queries
    Replies: 3
    Last Post: 01-11-2019, 08:49 AM
  2. How to sum quantities per group
    By crcastilla in forum Access
    Replies: 2
    Last Post: 03-21-2012, 10:05 AM
  3. Replies: 4
    Last Post: 06-25-2010, 12:32 PM
  4. Items at reorder point
    By MFS in forum Queries
    Replies: 2
    Last Post: 04-01-2010, 11:43 AM
  5. Adding Quantities?
    By prosbloom225 in forum Reports
    Replies: 0
    Last Post: 09-02-2009, 01:25 PM

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