Update first record for a number

[Heidestrand]

Hello community,

I have an issue with a recordset that I'm trying to solve but I somehow struggle with it. Couple of weeks ago the user arnelgp helped me out with a similar problem but I have troubles applying his method to my "new" situation.

What I want to do is: I want to compare dates again. But this time I want that for the first order number in column "sapsys_SAPNr" that he finds in my recordset he should compare the the dates of that order number. If there is a difference he should update the column "calenderdate" with the date from column "angelegtam".

But he should not touch the rest, on the first entry for an order number.

Here is an example:

For the order number "6231801" I have 5 records. In this case he only should compare the first record. As you can see in column "angelegtam" is 06.10.2014 and in "calenderdate" is 07.10.2014. Now he should update this date with the one from "angelegtam". But not the rest, only the first record.

I have tried this so far:

Sub Date()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varAngelegtAm As Variant
    Dim varKalendertag As Variant
    
    'Get the database and Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tblSAPSys Order By sapsys_calenderdate ASC", dbOpenDynaset", dbOpenDynaset)

    With rst
    If .RecordCount > 0 Then .MoveFirst
    While Not .EOF
            ' save order number and material number to variable, we'll use 'em later
            varAngelegtAm = ![sapsys_angelegtam]
            varKalendertag = ![sapsys_calenderdate]

    'Search for the first matching record   
    rst.FindFirst "[sapsys_SAPNr] And varAngelegtAm <> varKalendertag
    
    'Check the result
    If Not rst.NoMatch Then
	    .Edit
            ![sapsys_calenderdate] = ![sapsys_angelegtam])
            .Update
    .MoveNext
    EndIf
    
Wend
End With

End Sub

.. but it is not working. I'm not really sure what is missing.

I hope you can help me with this, really appreciate it

 

[CJ_London]

in databases (of all flavours) there is no concept of first/last/next/previous without a sense of order. When adding data to a table it will not go 'at the bottom' or 'at the top', just somewhere.

In your example, the autoID can provide an order - or perhaps a timestamp will do this if you have one. so if you are targeting the 'first/top' record specifically, you would want to identify the record which has the largest (max) autoid. 'First' without an order potentially could return any of your records for that particular SAPNo.

On the basis that autonumbers increment by one, the implication is that the 'first' record you have identified is in fact the 'last' one to be entered.

So, you need to define more clearly what you mean by 'first' by including an order.

From your code, you are ordering by calenderdate - but there are three records with the same date so within this subset you will still have a random order - ergo, the order by is insufficient.

 

[Heidestrand]

Hey CJ_London,
thank you for your answer and explanation (and sorry for my late reply).

The only order I wanted to apply is ordering the table by the column calenderdate. If he goes from the top of the table to bottom then it has the right order for the order numbers in column sapsys_SAPNr.
Because if you go with the autoid the dates are mixed up. So if you order them by calenderdate ascending, then I have the right order for the order numbers (when the order was placed, when it was changed, etc. in column calenderdate). With first one I mean the first order number he finds when he goes from top of my table towards the bottom.

Attached you will find the table I'm working with and the data. I would appreciate if you could check the file and maybe you understand me better then. if you have time of course.

If you order by sapsys_SAPNr then you will find some orders that appear more than one time (like in my example). And if you go to the left side and check the dates you will find them being mixed up.

What you could also do is pick one order number from sapsys_SAPNr, so show only this one and order the date. And here I want the first record for this order number to have the date from column angelegtam.
Hopefully you understand my problem better now.

 

[CJ_London]

I do understand - what I am saying is your ordering need to be complete - to get the order for your table as displayed you need

order by sapnr asc, calenderdate asc, sapid desc

if you don't have the sapid in the order, the 'first' record will be random within sapnr and calenderdate

 

[Heidestrand]

Thanks for your answer. When I apply that on my code I have to open a recordset like this:

Set rst = dbs.OpenRecordset("SELECT * FROM tblSAPSys Order By sapsys_SAPNr ASC, sapsys_calenderdate ASC, sapsys_autoid DESC", dbOpenDynaset)

.. right?

After this, is my following code correct? Or how can I proceed after this?

 

[CJ_London]

your code doesn't look like it will run - for example

rst.FindFirst "[sapsys_SAPNr] And varAngelegtAm <> varKalendertag

is missing an end quotation mark and [sapsys_SAPNr] needs to be equal to something.

and this

'Check the result
    If Not rst.NoMatch Then
    .Edit
            ![sapsys_calenderdate] = ![sapsys_angelegtam])
            .Update
    .MoveNext
    EndIf

will only advance to the next record if rst.NoMatch is false - not clear if this is what you want -but if it finds rst.NoMatch =true then it looks like you are in an endless loop.


Also varKalendertag is declared as a variant - not sure how that will compare with a date.

Perhaps, using the example data you have you can show what it is required to look like after this routine has run - use excel as a 'template' because I suspect you can do this in one query without the need for recordsets.

 

[Heidestrand]

Hey CJ_London,

for the first part: I didn't know to what [sapsys_SAPNr] could be equal. I only wanted to have something like "find first [sapsys_SAPNr]" having that ordering in my table.

It were just my thoughts, I didn't know how to code that :/

But honestly I'm not really sure how I can show you how it should look like after the procedure was executed. I just picked one order number in Excel now and ordered the columns like you mentioned it. This is how it should look like.
And the first entry for an order number, so when it appears in that column the first time, this entry should have the date from sapsys_angelegtam.

Is there another way I can do that? I don't have to use a recordset, I want to have it simple. But somehow it's pretty complicated..

 

[CJ_London]

sorry doesn't help - this is for a different sapnr - I need to see the before and after (any relevant sapnr) but the same sapnr for before and after

 

[Heidestrand]

In my table I have 12 sap numbers that appear more than one time.
6240509 appears 3 times and 6231801 appears 5 times.

I want to chose 6231801. I attached the Excel file with before and after.

Please tell me if this helps you now

 

[CJ_London]

yes that helps

try this code - you will probably need to correct table and field names

UPDATE tblSAPSys SET calenderdate = [angeletram]
WHERE autoID=(SELECT top 1 autoID from tblSAPSys as S where SAPNr=[tblSAPSys].[SapNr] and angeletram<>calenderdate order by calenderdate, autoid desc) AND SapNr=6231801

If you remove the 'AND SapNr=6231801' part then this will do all SapNr's where angeletram<>calenderdate

 

[Heidestrand]

Hey CJ_London,
first of all, thank you a lot for that!! That's really a big step in the right direction.

I've only one thing to add: Somehow the code must know that an entry was already changed. So every time I execute it it would change the next entry that is not equal.

What I mean is: when I'm inserting new data into this table (I do this one time, the last day of a month in every month), then the code would also change the already changed dates of existing numbers plus the new ones and writes wrong dates to the existing numbers.

So I need something like.. maybe an extra column where the code writes an "yes" for the ones that were touched and a "no" where it didn't change it.
And then when new data was inserted into the table I have nothing in this new column where the new data is and then it can process the new data.

Do you understand what I mean?

 

[CJ_London]

Somehow the code must know that an entry was already changed. So every time I execute it it would change the next entry that is not equal.

it should do that automatically because this bit

angeletram<>calenderdate

says it hasn't been changed.

If that is not what you mean -please provide an example

but it you wanted to do it your way, create a text field called Updated, no default value

 UPDATE tblSAPSys SET calenderdate = [angeletram], Updated="Yes"
WHERE autoID=(SELECT top 1 autoID from tblSAPSys as S where SAPNr=[tblSAPSys].[SapNr] and angeletram<>calenderdate and Updated is null order by calenderdate, autoid desc) AND SapNr=6231801

 

[MarlaC]

Also, you may want to add a True/False field to your table that is updated to True when the value is changed, if there are cases where the calendar date may change but you don't want the other date to change again.

 

[Heidestrand]

Thanks for this tip, MarlaC! I created a yes/no column and adjust CJ_London's query accordingly.
I figured out that when the query processed one month and set the true values for the numbers it changed then I will write another query that sets every other value to true that isn't true yet.
When another new month of data comes in the values are false and the query can't change the old ones anymore since they're all true.

I will try that on Monday, I'm not at work tomorrow. But I hope I can come back to you when I face a problem

Thanks a lot so far anyways, you really helped with this!

 

[MarlaC]

You're welcome! I've had to do something similar before and know how important it can be to protect 'set' values from being changed. I'll be happy to help again in the future if I can.