Running Total for Stock On Hand with Zero as a floor.

[Hugh McGill]

My problem is somewhat like a credit/debit/balance issue, but my balance cannot be negative.

Product BeginDate Stock Demand Balance
ABEEG01 08/1/2013 230 50 180
ABEEG01 09/1/2013 0 100 80
ABEEG01 10/1/2013 0 100 -20


ABEEG01 11/1/2013 0 70 -90
ABEEG01 12/1/2013 249 80 59
ABEEG01 01/1/2014 0 20 39

These are the results I'm getting with a running totals query. The problem is that I cannot have a negative balance of product. If I ended September with 80 and there was a demand for 100 in October, I would end October at 0 after selling all 80 that I had. The 20 I could not accommodate would simply be lost sales. When November comes around and I have demand for 70 more, but no stock, I don't dig a deeper hole, I hold with a zero balance. When December rolls around and more stock arrives, I can begin satisfying current demand again. I would begin with 249, sell 80 and finish the month with 169. I'd carry that 169 into January and sell 20 more to finish with 149. It should look like this.

Product BeginDate Stock Demand Balance
ABEEG01 08/1/2013 230 50 180
ABEEG01 09/1/2013 0 100 80
ABEEG01 10/1/2013 0 100 0
ABEEG01 11/1/2013 0 70 0
ABEEG01 12/1/2013 249 80 169
ABEEG01 01/1/2014 0 20 149

Basically, the running total needs to start over when the balance hits zero and the projected demand should be ignored until there is stock on hand to satisfy it. Can anyone help?

Thanks

[mrojas]

How about running an update query where the criteria is a negative number and the update value is zero?

[ssanfu]

What you are talking about is a query with a running sum but with a twist. From the first table, it looks like you have that working. Now you have to add the IIF() function to return a 0 when the balance (running sum) goes negative.


I am curious as to why the stock value for 9/1/2013 is 0 when it should be 180.....

[Hugh McGill]

How about running an update query where the criteria is a negative number and the update value is zero?

How could one do that inside a running totals query? I could certainly do that after the running totals ran, but I wouldn't have the right balance numbers. The idea here is that if the balance is <=0 the running sum must carry zero to the next record and so forth until stock is replenished as in the 12/1/2013 record in my table.

[Hugh McGill]

What you are talking about is a query with a running sum but with a twist. From the first table, it looks like you have that working. Now you have to add the IIF() function to return a 0 when the balance (running sum) goes negative.


I am curious as to why the stock value for 9/1/2013 is 0 when it should be 180.....

I tried that but never could get it to work. How can one write an IIF() statement inside a running sum? That is exactly what I need.

In my example, 'Stock' indicates new inventory coming online, a credit to the balance, but not the balance itself. In the first record I began with a new batch of 230 and had demand for 50 leaving me with a balance of 180. No new inventory arrived in September so the 'Stock' is zero. The balance carried forward is indeed 180 like you said. It satisfied September's demand of 100 yielding a final September balance of 80. See the pattern? My problems start in October when no additional stock is added and the carried forward balance of 80 is insufficient to satisfy October's demand for 100. My running totals query results in -20 which then gets carried forward. I can't have a negative balance and I can't carry a negative balance forward.

[mrojas]

I suppose that you manually enter the Stock and Demand quantities, correct? If you, are doing this directly into the table? Do you at any time reset the starting date? In the sample above, 08/01/2013 seems to be the beginning of your inventory. From that point forward I assume you'd like to only have to enter Demand, and if you receive new Stock, update the Stock this as well.
What do you want to happen if you receive stock and the previous Balance is greater than zero? I suppose you would want the newly received stock added to the Balance.

As Steve pointed out, there seems to be an error on record for 09/2013 and 01/2014. Is this correct?

[ssanfu]

Would you post the SQL of your query. I will try to modify it to add the IIF().... or one of the SQL wizards on the forum should be able to get it working. But we need to see the SQL first.

[Hugh McGill]

Absolutely! Here it is. Thanks for the help.

SELECT [tblStock Array].strProductID, [tblStock Array].BeginDate, [tblStock Array].OnHand, [tblStock Array].Demand, (SELECT Sum([XX].[OnHand]-[XX].Demand) FROM [tblStock Array] AS [XX] WHERE [XX].[strProductID] = [tblStock Array].[strProductID] AND [XX].[BeginDate] <= [tblStock Array].[BeginDate]) AS Balance INTO [tblStock Array Running]
FROM [tblStock Array]
ORDER BY [tblStock Array].strProductID, [tblStock Array].BeginDate;

[ssanfu]

Well, I had to admit defeat.

I couldn't get the results you wanted using strictly SQL.
But I was able to with a query and a UDF.

I made a table but didn't use spaces in the table name; I think I added all of the spaces back.

Here is the code:

Public Function CalcBalance(ThisDate As Date, pProduct As String) As Integer
   Dim r As DAO.Recordset
   Dim sSQL As String
   Dim RunningBal As Integer
   Dim ThisOnHand As Integer
   Dim ThisDemand As Integer

   'set default return value
   CalcBalance = 0
   'initalize variables
   RunningBal = 0

   ' Open recordset
   sSQL = "SELECT OnHand, Demand"
   sSQL = sSQL & " FROM [tblStock Array]"
   sSQL = sSQL & " WHERE [strProductID] = '" & pProduct & "' AND [BeginDate]<= #" & ThisDate & "#;"
      Debug.Print sSQL

   Set r = CurrentDb.OpenRecordset(sSQL)
   If Not r.BOF And Not r.EOF Then
      ' Populate the recordset
      r.MoveLast
      r.MoveFirst
      Do While Not r.EOF
         RunningBal = RunningBal + Nz(r!OnHand, 0) - Nz(r!Demand, 0)
         If RunningBal < 0 Then
            RunningBal = 0
         End If
         r.MoveNext
      Loop

   End If
   r.Close

   CalcBalance = RunningBal

   Set r = Nothing

End Function

Here is the query:

SELECT [tblStock Array].strProductID, [tblStock Array].BeginDate, [tblStock Array].OnHand, [tblStock Array].Demand, CalcBalance([begindate],[strProductID]) AS Balance
FROM [tblStock Array]
ORDER BY [tblStock Array].strProductID, [tblStock Array].BeginDate;

And the results
Attachment 14124

[Hugh McGill]

Thanks Steve. I purchased a Visual Basic book and have been studying functions and procedures (it's been a while since I posted this question and nobody had replied). I have been plowing down a very similar path, but what you've given me here is far more elegant. This is going to solve my problem. I really appreciate it.