when negative? 2

[koresnordic]

I have a small table that contains a list of upcoming sales orders and purchase orders. It also contains a "start" value (which is the current stock). I have generated a query that puts the transactions into item & date order. An example of the data is:
item date qty ref
9417402 01-Jan-01 83.00 START
9417402 26-Apr-05 -10.00 CO 504305
9417402 20-May-05 -100.00 CO 503954
9417402 06-Jun-05 400.00 P530303

So following the course of events, this product would go negative on the 20th of May. What I need is to record the date the stock goes negative in another table (which consists of just the item and date negative). It may never go negative or it may go negative, get stock in so go positive, then go negative again - in which case it is the first date it goes negative we need. Is this possible or am I asking too much? I know some of you like a challenge, so I hope you enjoy this one.



Graham

 

[Tranman]

Graham,
People usually solve problems like this with cursors (recordsets). They allow you to easily move through your data, looking at one row at a time, until you identify the situation you need to identify, if it exists.

I would be happy to write you a little VBA snippet that will identify the "going negative" date and insert it and the item id into a different table. What I would need from you is the SQL for your query that creates the dataset above.

Let me know.

Tranman

 

[r937]

untested, but this is the general idea --

select T1.item
     , min(T2.date) as first_neg_date
  from yourtable as T1
left outer
  join yourtable as T2
    on T1.item = T2.item
   and T1.date < T2.date  
group
    by T1.item
having sum(T2.qty) < 0

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[koresnordic]

Tranman,
Thx for the offer. The SQL is very basic as the transactions are already in the table.

SELECT transactions.item, transactions.date, transactions.qty, transactions.ref
FROM transactions
ORDER BY transactions.item, transactions.date;


With the results going into a table called "date negative", fields "item" and "negative".



At the moment, the only way I am doing this is to export the transactions list into excel and using a couple of IF() statements to find when it goes negative and then importing the data back into access to update the file. So your offer is very much appreciated.



Graham

 

[koresnordic]

Rudy - thx for the code. I have tried it but it seems to give me the first date for a transaction even if the product doesn't go negative. I have also noticed that the item given in my example doesn't appear in the result. Appreciate the time given to this.



Graham

 

[lespaul]

this is why it is not recommended to store calculated fields, you are always having to modify something in order to make sure the calculation is correct. you should be able to determine on the fly using a query if a particular stock item is/will be negative.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Tranman]

Graham,
Here ya go. What this code does is get a list of all of the items (in item order), then processes each item individually, in ascending transaction date order. When an item goes negative, it writes the info to the other table, then moves on to the next item.

There are a couple of debugs and a msgbox commented out if you want to run the code manually and watch what it's doing.

You didn't say if you are familiar with VBA, and how to put the code into a module, and utilize it. If this is a problem for you, write back and we can cover that.

Good luck with your project.

Tranman

Private Sub WentNeg()
Dim dblQty As Double
Dim rsDist As New ADODB.Recordset
Dim rsTran As New ADODB.Recordset
Dim strIns As String
Dim strSQL As String
rsDist.Open "Select distinct item from transactions order by item", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rsDist.MoveFirst
Do While Not rsDist.EOF
  strSQL = "Select item, date, qty, ref from transactions where item = '" & _
           rsDist.Fields("item") & "' order by item, date"
  'Debug.Print strSQL
  rsTran.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  dblQty = rsTran.Fields("qty")
  rsTran.MoveNext
  Do While Not rsTran.EOF
    Select Case dblQty + rsTran.Fields("qty")
      Case Is < 0
        'MsgBox rsTran.Fields("item") & " went negative: " & rsTran.Fields("date"), vbOKOnly, "WENT NEG"
        strIns = "Insert into [date negative] values ('"
        strIns = strIns & rsTran.Fields("item") & "', #"
        strIns = strIns & rsTran.Fields("date") & "#)"
        'Debug.Print strIns
        CurrentProject.Connection.Execute strIns
        Exit Do
      Case Else
        dblQty = dblQty + rsTran.Fields("qty")
    End Select
    rsTran.MoveNext
  Loop
  rsTran.Close
  Set rsTran = Nothing
  rsDist.MoveNext
Loop

rsDist.Close
Set rsDist = Nothing
End Sub

 

[koresnordic]

Excellent tranman - will try it a bit later and let you know how I get on.



Graham

 

[koresnordic]

Great work Tranman . This did excatly what I needed (haven't a clue how thou ). Much appreciated. Theonly problem I had was an unregistered dll causing the "currentproject.connection" to fail. I can now complete the task in hand.



Graham

 

[koresnordic]

OK. Hit a snag. The date is not correct. I know what it is doing but not why.
e.g

item date qty ref
9410602 01-Jan-01 211.00 START
9410602 03-Jun-05 -250.00 CO 504515

This obviously goes negative on the 3rd of June. But the result given is the 6th of March. Denoting both as numerical dates shows the correct date as 03/06/05 and the incorrect date as 06/03/05. You may have guessed that I am UK based by the DDMMYY format of the date. It seems to be transposing the DD & MM elements. Not every date is transposed this way, only those that would make accurate dates. e.g.

item date qty ref
9414302 01-Jan-01 0.00 START
9414302 13-May-05 -20.00 CO 503641


Numerically 13/05/05 will not transpose to 05/13/05 as there is no 13th month and this is not a valid date.

If you could work your magic again Tranman and let me know what I am doing wrong, it would save my sanity


p.s. apologies for the smilies, but I am an old gamer at heart and populate several forums where these are frequently used. I think it adds a bit of colour to precedings.



Graham

 

[PHV]

Replace this:
strIns = strIns & rsTran.Fields("date") & "#)"
By this:
strIns = strIns & Format(rsTran.Fields("date"), "yyyy-mm-dd") & "#)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tranman]

Graham,
I had wondered about your location. I work quite a bit with the guys at EXOR Corp. (UK). Their Highways application is Oracle-based, but we have had more than a few discussions about dates (and KM vs miles--I am a TRANman).

Of course you can modify PHV's format statement to deliver the date in your normal format instead of YYYYMMDD, if you wish.

PHV-good catch.

Good luck,
Tranman

 

[koresnordic]

Thx PHV that got it.



Graham