• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 712
  • Last Modified:

SQL/VBA FIFO Stock Issue

Hi

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.

Thanks
0
Murray Brown
Asked:
Murray Brown
  • 2
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How are you storing the stock data? Can you give us more information about the structures and data transactions?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
  Else
    dCurrQty = dCurrQty + rst("Number")
  End If

GetOldest = #01/01/1900#
  rst.MoveNext
Loop

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
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much. I appreciate the extra time taken
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now