how to check if value already exists in the past

[Heidestrand]

Hello,

I want to update a column by checking a condition if an order number already exists in the past. I coded some SQL but it doesn't work how I want

UPDATE tblSAPSys AS a INNER JOIN tblDatenAusExcelNeu AS b ON a.sapsys_SAPNr = b.Verkaufsbeleg SET a.sapsys_date = b.Kalendertag
WHERE EXISTS
(SELECT *
FROM tblSAPSys As c 
WHERE c.sapsys_SAPNr = a.sapsys_SAPNr AND c.sapsys_date < a.sapsys_date);

a.sapsys_SAPNr is my column with order numbers. So the query should check if an order number in this column already exists in the past by the help of the date in column sapsys_date. If yes, then it should copy the values from column Kalendertag into sapsys_date.

When I execute the query, nothing happens. But I don't know what's wrong with it.

I appreciate any help from you!

 

[arnelgp]

will this help:

UPDATE tblSAPSys AS a INNER JOIN tblDatenAusExcelNeu AS b ON a.sapsys_SAPNr = b.Verkaufsbeleg SET a.sapsys_date = b.Kalendertag
WHERE a.sapsys_SAPNr IN
(SELECT sapsys_SAPNr FROM tblSAPSys As c
WHERE c.sapsys_date < a.sapsys_date);

 

[Heidestrand]

Thank you for your help Unfortunately it didn't help

See, I have this table:

In column Verkaufsbeleg there are my order numbers, AngelegtAm is the date when the order was placed and Kalendertag is the date when the order was changed.

In my other table the date hasn't changed after executing the query:

For the date in my second record set there must be 14.10.2011 and not 30.09.2015, because the order was already placed 30.09.2015 and only undergoes a change in October.

 

[arnelgp]

how about this:

UPDATE tblSAPSys AS a INNER JOIN tblDatenAusExcelNeu AS b ON a.sapsys_SAPNr = b.Verkaufsbeleg SET a.sapsys_date = b.Kalendertag WHERE (a.sapsys_date=b.AngelegtAm) And (b.Kalendartag > b.AngelegtAm)

 

[Heidestrand]

Thank you again, arnelgp, for the code.

This time it took all the dates from column Kalendertag and changed the dates in column sapsys_date.
So the end result is this:

But based on my table with the dates it should write Oct 14th into line 1 and Oct 19th into the second and third line. This is the root table:

 

[arnelgp]

on your table " tblDatenAusExcelNeu" does the date field Kalendertag have the same value in all records of a particular order? are there anything we can check beside the order number, do you also have product id (number) to compare with in the query? if so please the field name for each table.

 

[Heidestrand]

You can also check for the id, it's an automatically generated number.

So this is the table where I want to have the date changed:

Right now I insert the order number and the date when the order was placed into this table.

The date comes from this table:

So my job is to bring two date fields into one. In table tblSAPSys you see three entries for one order (sapsys_SAPNr).
Currently it is the date from the column AngelegtAm. But as you see there is a date which is later than AngelegtAm, in this case it's in column Kalendertag (calendar day).
So he must see: ok, for the first entry there is no predecessor for this order in the past according to the date, so let's keep the current date from AngelegtAm. But for the second entry there aready is an entry in the past (ok, the same day, but there already is one). So let's update the order with the date from Kalendertag.

I think, it's good when we use the ID as well.. maybe this is important..

EDIT:
I just figured something out that could work:

UPDATE tblSAPSys AS a INNER JOIN tblDatenAusExcelNeu AS b ON a.sapsys_SAPNr = b.Verkaufsbeleg SET a.sapsys_date = b.Kalendertag
WHERE a.sapsys_SAPNr IN 
(SELECT sapsys_SAPNr
FROM tblSAPSys AS c 
WHERE c.sapsys_SAPNr = a.sapsys_SAPNr AND c.sapsys_autoid < a.sapsys_autoid AND c.sapsys_date <= a.sapsys_date);

Will do some tests to see if the logic works for me..

EDIT 2:

Ok, it is not perfectly working. See this: for one order number have three different dates:

The order was placed Nov 16th, but was changed two times, in fact for 28th and 29th.
But my logic does this:

So it's not touching Nov 28th. Any idea how to fix this?

 

[arnelgp]

so tblSapSys has autonumber field while the other does not, well need a vba to work aroud this, if you would agree.

 

[Heidestrand]

Anything that solves this problem is good for me

tblDatenAusExcelNeu has no auto number. Here I don't need this because I analyze this table every time and delete it and write new entries in it.

 

[arnelgp]

here paste this in a module and run in immediate window

Public Sub UpdateSapSysDate()

    Dim dbs As DAO.Database
    Dim rstSapSys As DAO.Recordset
    Dim rstDaten As DAO.Recordset
    Dim varOrderNumber As Variant
    Dim varOrderDate As Variant
    
    Set dbs = CurrentDb
    ' open our tables
    Set rstdsapsys = dbs.OpenRecordset("Select * tblSAPSys Order By sapsys_autoid", dbOpenDynaset)
    Set rstDaten = dbs.OpenRecordset("tblDatenAusExcelNeu", dbOpenSnapshot)
    DoCmd.Hourglass True
    With rstSapSys
        If .RecordCount > 0 Then .MoveFirst
        While Not .EOF
            ' save order number to variable, well use it later
            varOrderNumber = ![sapsys_SAPNr]
            ' find order number in tblDatenAusExcelNeu
            rstDaten.FindFirst "[AngelegtAm] = " & varOrderNumber
            If Not rstDaten.NoMatch Then
                ' if we found it were lucky!
                Do While (varOrderNumber = ![sapsys_SAPNr])
                    ' compare the dates
                    ' we need to update only if:
                    ' 1. sapsys_date = AnglegtAm and
                    ' 2. AnglegtAm date < Kalendartag ?
                    If ![sapsys_date] = rstDaten![AngelegtAm] And rstDaten![AngelegtAm] < rstDaten![Kalendartag] Then
                        .Edit
                        ![sapsys_date] = rstDaten![Kalendartag]
                        .Update
                    End If
                    .MoveNext
                    ' exit loop if we are end of file pointer
                    If .EOF Then Exit Do
                    rstDaten.FindNext "[AngelegtAm] = " & varOrderNumber
                    ' we also exit if there no more match
                    If rstDaten.NoMatch Then Exit Do
                Loop
            End If
        Wend
    End With
    Docmd.HourGlass false

    ' close objects
    Set rstSapSys = Nothing
    Set rstDaten = Nothing
    Set dbs = Nothing
    msgbox "done!"
End Sub

 

[Heidestrand]

Thanks a lot for the code!!

I know you did a great job, but it is not completely working. When I execute it Access starts loading and loading, but nothing happens. It's not responding anymore.
I think it got stuck in a loop or something like that. I also fixed the code a lil bit, there were some syntax errors.

EDIT:
Now it tells me: End If without If Block at the end

 

[arnelgp]

sorry about that here try again:

Public Sub UpdateSapSysDate()

    Dim dbs As DAO.Database
    Dim rstSapSys As DAO.Recordset
    Dim rstDaten As DAO.Recordset
    Dim varOrderNumber As Variant
    Dim varOrderDate As Variant
    
    Set dbs = CurrentDb
    ' open our tables
    Set rstdsapsys = dbs.OpenRecordset("Select * tblSAPSys Order By sapsys_autoid", dbOpenDynaset)
    Set rstDaten = dbs.OpenRecordset("tblDatenAusExcelNeu", dbOpenSnapshot)
    DoCmd.Hourglass True
    With rstSapSys
        If .RecordCount > 0 Then .MoveFirst
        While Not .EOF
            ' save order number to variable, well use it later
            varOrderNumber = ![sapsys_SAPNr]
            ' find order number in tblDatenAusExcelNeu
            rstDaten.FindFirst "[AngelegtAm] = " & varOrderNumber
            If Not rstDaten.NoMatch Then
                ' if we found it were lucky!
                DoEvents
                Do While (varOrderNumber = ![sapsys_SAPNr])
                    ' compare the dates
                    ' we need to update only if:
                    ' 1. sapsys_date = AnglegtAm and
                    ' 2. AnglegtAm date < Kalendartag ?
                    If ![sapsys_date] = rstDaten![AngelegtAm] And rstDaten![AngelegtAm] < rstDaten![Kalendartag] Then
                        .Edit
                        ![sapsys_date] = rstDaten![Kalendartag]
                        .Update
                    End If
                    DoEvents
                    .MoveNext
                    ' exit loop if we are end of file pointer
                    If .EOF Then Exit Do
                    rstDaten.FindNext "[AngelegtAm] = " & varOrderNumber
                    ' we also exit if there no more match
                    If rstDaten.NoMatch Then Exit Do
                Loop
            Else
                .MoveNext
                DoEvents
            End If
            
        Wend
    End With
    
    ' close objects
    Set rstSapSys = Nothing
    Set rstDaten = Nothing
    Set dbs = Nothing
End Sub

 

[Heidestrand]

Thank you again, arnelgp, for your great help!

So the code works and most of it is correct. When testing my data I stumbled upon one situation where it didn't really work I guess. Look at this order number:

It was placed Nov 10th and changed Nov 11th two times. In my other table it wrote me these date information:

Maybe there is another information that can be used: the material ID. In table tblSAPSys the column is called sapsys_KMATID and in table tblDatenAusExcelNeu the column is called MaterialID.

The thing is that I couldn't find the material ID at Nov 17th, but Nov 16th. So maybe these two IDs also need to be the same (as another condition).

Anyways, I'm really thankful for your help, I really appreciate it!

Edit:

So I was playing around with it more and I found another mistake
Here the order was placed in March 5th and changed March 20th:

But in my table it copied the wrong data, in this case it wrote March 14th:

 

[arnelgp]

again plz., i change the index and included matid in comparing:

Public Sub UpdateSapSysDate()

    Dim dbs As DAO.Database
    Dim rstSapSys As DAO.Recordset
    Dim rstDaten As DAO.Recordset
    Dim varOrderNumber As Variant
    Dim varOrderDate As Variant
    Dim varMatID As Variant
    
    Set dbs = CurrentDb
    ' open our tables
    Set rstdsapsys = dbs.OpenRecordset("Select * tblSAPSys Order By sapsys_KMATID, sapsys_autoid", dbOpenDynaset)
    Set rstDaten = dbs.OpenRecordset("tblDatenAusExcelNeu", dbOpenSnapshot)
    DoCmd.Hourglass True
    With rstSapSys
        If .RecordCount > 0 Then .MoveFirst
        While Not .EOF
            ' save order number to variable, well use it later
            varOrderNumber = ![sapsys_SAPNr]
            varMatID = ![sapsys_KMATID]
            ' find order number in tblDatenAusExcelNeu
            rstDaten.FindFirst "[AngelegtAm] = " & varOrderNumber & " And [Verkaufsbeleg] = " & varMatID
            If Not rstDaten.NoMatch Then
                ' if we found it were lucky!
                DoEvents
                Do While (varOrderNumber = ![sapsys_SAPNr] And ![sapsys_KMATID] = varMatID)
                    ' compare the dates
                    ' we need to update only if:
                    ' 1. sapsys_date = AnglegtAm and
                    ' 2. AnglegtAm date < Kalendartag ?
                    If ![sapsys_date] = rstDaten![AngelegtAm] And rstDaten![AngelegtAm] <> rstDaten![Kalendartag] Then
                        .Edit
                        ![sapsys_date] = rstDaten![Kalendartag]
                        .Update
                    End If
                    DoEvents
                    .MoveNext
                    ' exit loop if we are end of file pointer
                    If .EOF Then Exit Do
                    rstDaten.FindNext "[AngelegtAm] = " & varOrderNumber & " And [Verkaufsbeleg] = " & varMatID
                    ' we also exit if there no more match
                    If rstDaten.NoMatch Then Exit Do
                Loop
            Else
                .MoveNext
                DoEvents
            End If
            
        Wend
    End With
    
    ' close objects
    Set rstSapSys = Nothing
    Set rstDaten = Nothing
    Set dbs = Nothing
End Sub

 

[Heidestrand]

Thank you again, arnelgp for your amazing help!
And sorry I couldn't write back earlier. I made some adjustments to your code and so far it works very well. But I have to do some more tests when I'm back at work on Monday. If I need more help I will come back to you again

So far, thanks a lot for your help

 

[Heidestrand]

Hey arnelgp, it's me again. I wanted to expand the code a little bit and I guess I need your help for that again
It's about this part:

                    If ![sapsys_date] <> rstDaten![Kalendertag] And ![sapsys_KMATID] = rstDaten![MaterialID] Then
                        .Edit
                        ![sapsys_date] = rstDaten![Kalendertag]
                        .Update
                    End If

I want to tell him that he should use the column rstDaten![AngelegtAm] when there is no record in the past for the same ![sapsys_SAPNr] number.

So I wanted to code something like:

                    If ![sapsys_SAPNr].CurrentRecord = 1 Then
                        .Edit
                        ![sapsys_date] = rstDaten![AngelegtAm]
                        .Update
                    End If

But I get the run time error 438: Object doesn't support this property or method.

Do you know a way how to do that?

Or is it possible to skip the first record? So "Do While (varOrderNumber = ![sapsys_SAPNr] And ![sapsys_KMATID] = varMatID)" but skip the first match and update starting from the second one..