Creat a query that outputs a stockout date after on hand parts are used.

[Deweyjosh]

Hello,
I am trying to crate a query that will provide an output row with the part number and the short date.
Query 1
Has the part number/ usage date / usage qty

Query 2
Has part number / on hand inventory

Example
Item. Usage date. Qty. used
548-542 4/6/2018 1
548-542 4/16/2018 2
548-542 4/24/2018 1
548-542 5/2/2018 1
548-542 5/10/2018 1
548-542 5/18/2018 1
548-542 5/25/2018 1
645-214 4/9/2018 2
645-214 5/4/2018 2
645-214 5/8/2018 1
645-214 5/23/2018 2
645-214 6/21/2018 2
645-214 7/6/2018 1

Query 2
Item. Current Qty.
548-542 5
645-214 4

So the desired out put would be

Item. Short date
548-542. 5/10/2018
645-214. 5/8/2018

Thanks

 

[arnelgp]

Explain more about the date output, what is your criteria of choosing such.

 

[Deweyjosh]

Hello,
The date output needs to be when all the current quantity is used. So Item 548-542 currently has qty. 5 Therefore,4/6/18, 4/16/18, 4/24/18, 5/2/18, This uses all the current quantity so 5/10/18 it the date that needs to be shown.

548-542 4/6/18 1
548-542 4/16/18 2
548-542 4/24/18 1
548-542 5/2/18 1
548-542 5/10/18 1

Thanks

 

[arnelgp]

copy this function in a Standard Module in VBE (Menu->Insert->Module):

Option Compare Database
Option Explicit

Dim db As DAO.Database

Public Function fnStockOutDate(partNumber As Variant, remainingBalance As Integer) As Date
    
    '* Note:
    '*
    '* replace query1 with the name of your first query
    '* replace all column name with correct column name from query1
    '*
    '* replace query1 with the name of your second query
    '*
    Dim totalOut As Integer
    Dim rs As DAO.Recordset

    If db Is Nothing Then Set db = CurrentDb
    '* open recordset from query1 with only relevant partNumber
    Set rs = db.OpenRecordset("select [usage date], [usage qty] from query1 " & _
                            "where [part number]=" & Chr(34) & partNumber & Chr(34) & " " & _
                            "order by [usage date] asc", _
                                dbOpenSnapshot)
    '* get the inventory quantity from query2
    totalOut = DLookup("[on hand inventory]", "query2", "[part number]=" & Chr(34) & partNumber & Chr(34))
    
    '* arbitrary value
    fnStockOutDate = Date
    
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        Do While Not .EOF
            totalOut = totalOut - ![usage qty]
            .MoveNext
            If totalOut <= 0 Then
                Exit Do
            End If
        Loop
        If Not .EOF Then fnStockOutDate = ![usage date]
        .Close
    End With
    Set rs = Nothing
End Function

create a new query against query2 and use this function:

SELECT Query2.[part number], fnStockOutDate([part number],[on hand inventory]) AS [date]
FROM Query2;