SQL/VBA FIFO Stock Issue

Posted on 2012-09-11
Last Modified: 2012-09-19

I have a VBA project where I need to build a mechanism to handle the FIFO
or First In First Out method of handling stock.

Has anyone perhaps built anything in this area?

I need to loop through the earliest incoming stock amounts and pull my stock
from this.

Question by:murbro
    LVL 84
    How are you storing the stock data? Can you give us more information about the structures and data transactions?

    Author Comment

    I have a stock table with DateIn and Number as two fields used for FIFO. When I get the number ordered, I need to look for the date furthest back where there is enough stock. So I look at Number and DateIn. If there isn't enough at that date then I need to look at the next date and add this to the count, and so on
    LVL 84

    Accepted Solution

    You'll have to use a Function or Stored Procedure to do this, I would think. Something like:

    Function GetOldest(PartNumber As String, QtyNeeded As Double) As Date

    Dim rst As DAO.Recordset
    Set rst = Currentdb.OpenRecordset("SELECT * FROM YourTable WHERE YourPartNumber=" & PartNumber & " ORDER BY DateIn DESC")

    Dim dCurrQty As Double
    Do Until rst.EOF
      dCurrQty = rst("Number")

      If dCurrQty > QtyNeeded Then
        GetOldest = rst("DateIn")
         Exit Function
        dCurrQty = dCurrQty + rst("Number")
      End If

    GetOldest = #01/01/1900#

    End Function

    So the function would return either (a) the oldest date at which you could fulfill the Qty Needed  or (b) 01/01/1900

    Author Closing Comment

    Thanks very much. I appreciate the extra time taken

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now