Query For FIFO Based Inventory System

Question

I have a table TABLE1 with following fields and data:

ID Date Units Rate

1	11/6/2011	718	14.26
2	6/7/2011	444	37.79
3	4/8/2011	695	42.11
4	5/9/2011	757	23.99
5	16/9/2011	-2000	12.32
6	15/10/2011	486	35.87
7	11/11/2011	292	19.29
8	26/11/2011	-1000	16.19
9	25/12/2011	542	46.06

xDate being in the format (d/m/yyyy)

Any idea of a query to determine the cost of Units On Hand on the FIFO basis as following:

6	15/10/2011	100	35.87
7	11/11/2011	292	19.29
9	25/12/2011	542	46.06

In other words having the negative (Outflow) entries of 3000 (i.e. 2000 + 1000) being adjusted with the oldest entries and remaining balance of the latest ones being shown.

Answer 1

Hi Faraz,

Pleased the reply helped. I agree about deleting records - if this was being done for real then I'd make sure table1 was a copy of the real records i.e. I'd only be using it to do the calculations and display the results. It's all depend on what else the main table was used for really.

Answer 2

I sort of got interested in this and as always there was a bit more to do than I first thought of. 

This is a real hacked bit of code with no error checking etc. but it does work on the Table1 you supplied. First you'll need a couple of queries that I've imaginatively called query1 and query2

query1 (SQL view) :-

Select sum(units)

from Table1

query2 (SQL view):-

SELECT *

FROM Table1

WHERE units > 0

ORDER BY id;

 This is the VBA code which I hope will give you a good start - fingers crossed:-

Sub FIFO()

Dim db As Database

Dim rs As Recordset

Dim Balance As Long

Dim UpdateAmt As Long

Dim Id As Long

'Connect to the database

    Set db = CurrentDb

    Set rs = db.OpenRecordset("Query2")

'Get the balance which should stay the same overall

    Balance = GetBalance()

'Starting at the bottom ...

    rs.MoveLast

'... keep moving up until the balance goes negative

    While Not rs.BOF And Balance > 0

        UpdateAmt = Balance 'this line just remembers what the update amount needs to be

        Balance = Balance - rs.Fields(2)

        rs.MovePrevious

    Wend

'move back up one so we put the update amount in the right place

     rs.MoveNext

'The next 3 lines just do the update

     rs.Edit

     rs.Fields(2) = UpdateAmt

     rs.Update

'Finally delete the rest of the records

     rs.MovePrevious

     Id = rs.Fields(0)

     DoCmd.SetWarnings False

     DoCmd.RunSQL ("Delete from table1 where units < 0")

     DoCmd.RunSQL ("Delete from Table1 where id <= " & Id)

     DoCmd.SetWarnings True

End Sub

Function GetBalance() As Long

Dim db As Database

Dim rs As Recordset

    Set db = CurrentDb

    Set rs = db.OpenRecordset("Query1")

    rs.MoveFirst

    GetBalance = rs.Fields(0)

End Function

Answer 3

Hi Faraz, The good news is that I understand what you're trying to achieve. The bad news is I don't think it can be achieved with a query. Personally I think you'll need to resort to VB. You can then traverse the dataset and achieve what you want algorithmically. As far as I can see you'll be staring at the oldest entry and leaving each unchanged until the outflows are accounted for and once this is achieved you place the remaining overall balance in the final entry (id 6 in your example).

Answer 4

Thanx for your reply Karl, however, I don't understand what confuses you? Consider the sample again. 3000 Units have been lost. I need the remaining 934 Units be represented by the last entries 3 entries, i.e. of 15-Oct, 11-Nov & 25-Dec

Answer 5

I do not understand what you want but it looks like you can set criteria on Units as >0 and it will give you that results.