Results 1 to 7 of 7
  1. #1
    bojamba is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    3

    Queries for FIFO Matching - Breaking quantities into each and matching by date




    0down votefavorite

    Way out of my depth here - hoping to get help.
    I use Access to arrange 3 sets of data. Promise Date, Delivery Date and Production Date to look for On Time Delivery, On Time Production, Etc.
    There are no links that pair these 3 tables. We might build 5 pieces and ship 3 to one and 2 to another, so I can't do a lookup based on quantity movement.
    Instead, I break each table into "Eaches" by date and match them in a FIFO fashion.
    Thus the first unit produced probably belongs to the first unit shipped, etc.
    This requires a tremendous amount of manual manipulation, primarily sorting by date and putting back into Access.
    Is there a way to take a table, break it into eaches, and sort & count that table by date?
    It would take this:
    Prod / Date / Qty X / Jan-10 / 2 Y / Feb-10 / 2 Z / Mar-10 / 2
    And turn it into this:
    Prod / Date / Qty / Order X / Jan-10 / 1 / 1 X / Jan-10 / 1 / 2 Y / Feb-10 / 1 / 1 Y / Feb-10 / 1 / 2 Z / Mar-10 / 1 / 1 Z / Mar-10 / 1 / 2
    I could link the tables together from there - but can't figure out how to do it outside of Excel sorting and formulas that loWay out of my depth here - hoping to get help.


    Way out of my depth here - hoping someone can help me.

    I use Access to arrange 3 sets of data. Promise Date, Delivery Date and Production Date to look for On Time Delivery, On Time Production, Etc.

    There are no links that pair these 3 tables. We might build 5 pieces and ship 3 to one and 2 to another, so I can't do a lookup based on quantity movement.
    Instead, I break each table into "Eaches" by date and match them in a FIFO fashion.
    Thus the first unit produced probably belongs to the first unit shipped, etc.
    This requires a tremendous amount of manual manipulation, primarily sorting by date and putting back into Access.
    Is there a way to take a table, break it into eaches, and sort & count that table by date?
    It would take this:
    Prod / Date / Qty
    X / Jan-10 / 2
    Y / Feb-10 / 2
    Z / Mar-10 / 2

    And turn it into this:
    Prod / Date / Qty / Order
    X / Jan-10 / 1 / 1
    X / Jan-10 / 1 / 2
    Y / Feb-10 / 1 / 1
    Y / Feb-10 / 1 / 2
    Z / Mar-10 / 1 / 1
    Z / Mar-10 / 1 / 2

    I could link the tables together from there - but can't figure out how to do it outside of Excel sorting and formulas that look at rows above.




  2. #2
    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
    16,716
    Note: I have approved your post so others can see and respond accordingly. However, it seems you have copied and pasted parts of the code twice.
    This has been cross posted https://stackoverflow.com/questions/...ching-by-each#

    When you cross post, you should include :
    -a comment saying you have done so, and
    - a link(s) to the other posting.


    Here is why



    For your benefit, you should describe your set up in plain English.
    It will help you with the logic to implement FIFO.
    Here are some links to youtube videos and articles discussing FIFO.

    https://www.oldschoolvalue.com/blog/...ory-valuation/
    https://www.accountingdetails.com/fi...ls_costing.htm
    https://www.youtube.com/watch?v=oshtlj9NtMQ
    https://www.youtube.com/watch?v=XCQhImjhYmY

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    really struggling to understand what you are asking. You talk of three tables and show 1. And it appears you have a record with a quantity of 2 from which you want to create 2 records each with a quantity of 1. Perhaps try again, using something closer to real life. What is the business? Is this a production, logistics, finance or some other issue?

  4. #4
    bojamba is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    3
    I'll try to explain better.

    We forge and machine large industrial components. One customer might have a project that requires 3 made from the same design. Another customer might need 2 more of that same design.

    To meet the demand, we'd build these in a lot of 5 to reduce setup costs.

    So I have a table of production orders that says I have 5 due to be produced on 3/1.

    Meanwhile I have an order table with promise dates - one says 3 for 4/1, and the other says 2 for 4/15.

    Let's say production is late and actually finishes them on 4/10. One of my orders will be late, the other is on time.

    When I multiply this across hundreds of designs and orders, it is hard to determine our company's "On Time Delivery (or Production)" hit rate.

    The problem is because I have one order of 5 that I can't match to an order of 2 and 3. There is no data linking the two together other than the part number and the dates.

    So what I have done is break the 5 into 1,1,1,1,1 that all have the 4/10 production date. Then I break my order table into 1,1,1 with a 4/1 date and 1,1 with a 4/15 date.

    Up to this point I'm in access, but now I need to create a running count, or running sum starting with the earliest production moving to the most recent production.

    I don't know how to do that in access, so I move it to excel, sort it by date, and then add a count.

    So I have 1-1 on 4/10, then 1-2 on 4/10, then 1-3 on 4/10, etc.

    I do the same with orders, creating 1-1, 1-2, 1-3 on 4/1 and then 1-4, 1-5 for 4/15.

    At this point I can link the production dates with the order dates across all my data and come up with our on time production info.

    So in a nutshell, I'm looking for a query that can create a running count (or sum?) that resets at 1 for each change in part number or production date.

    Or perhaps someone has an altogether more elegant solution to this whole concept!

    As for "cross posting" i posted on stack overflow, and they told me because it was access and not programming, that it was off topic. So I came here.

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you upload a sample of your tables and any related queries (that you use after your Excel manipulations) to see what you are trying to achieve. You must have some "temporary" tables to put your Excel data in, so please include those as well. This should be easy to program in VBA once you give us some more info.

    Cheers,
    Vlad

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    And when you have to produce 200 pcs of some part, you'll have 200 ProdOrder's?

    The simplest way will be an ProdOrder per Part of CustomerOrder (or per Part of Delivery, when CustomerOrder is for longer period and must be splitted, or per production date).

    So for your example you'll have 2 ProdOrder's, to produce 3 parts and 2 parts respectively. When you produce them on separate days, then materials for both ProdOrder are prepared for those days. When you produce them on same day, then materials are prepared for same day, but in separate lots anyway.

    ProdOrder is best/simplest to manage as for certain operation/set of operations on certain workplace on certain date/week for certain part for certain customer. And preferably it has Customer Order ID as FK.

    In our factories, there my be hundreds of different ProdOrder's to produce some product, or may be only some - depending on complexity of production.

  7. #7
    zed ziyaad is offline Novice
    Windows 8 Access 2007
    Join Date
    Jan 2021
    Posts
    1
    noob test noob test noob test noob test

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

Similar Threads

  1. Find matching data between 2 queries
    By michael.p.ryan1 in forum Access
    Replies: 3
    Last Post: 08-24-2012, 05:51 PM
  2. Replies: 1
    Last Post: 10-24-2011, 08:01 AM
  3. Join queries that do not have matching field
    By Petefured in forum Queries
    Replies: 2
    Last Post: 05-04-2010, 11:19 AM
  4. Comparing Dates And Matching Quantities
    By chrisleng in forum Programming
    Replies: 0
    Last Post: 02-23-2010, 10:50 AM
  5. Partial Matching based Queries
    By Yatesb in forum Queries
    Replies: 0
    Last Post: 02-23-2009, 01:06 PM

Tags for this Thread

Posting Permissions

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