SET value to next record in group

[pio92]

Hi,


I looking for a SQL-Query (MS-Access O365) ,which:


If status is "in"
then the next record with the same F7 receives value "out"




I want updating so my table.
In this table are more "groups" with difficult F7 value

[June7]

If "in" has just been entered, why not just move to next record and type "out"?

I very much doubt SQL alone can accomplish and will require VBA procedure. However, is not clear to me what exactly you want to accomplish, what process you want to automate. Do you want to update entire table?

You show table in image. Any programmatic solution would have to be initiated by an event, perhaps button click on form.

[CJ_London]

then the next record with the same F7 receives value "out"

you need to be clear what you mean by 'next record' - I assume it would that be based on F14 - so the next record in time and not next record that was entered (probably based on ID), regardless of time. Your example is simplistic so either basis could work.

Also need to clarify what happens with the other records - would appear from your example that 'in' is already populated so you only want to populate the next record (ID=4) - what if record 6 was populated with 'in'? - there is no next record.

what happens if you subsequently edit record 3 and change the time to a later time? or remove or change the status? or change record 4 to an earlier time? or add a new record with a time between those of records 3 and 4?

Agree with June - this cannot be automated in a table, you will need to use a form and vba. What that vba looks like depends on clarifying what you are trying to do

[pio92]

you need to be clear what you mean by 'next record' - I assume it would that be based on F14 - so the next record in time and not next record that was entered (probably based on ID), regardless of time. Your example is simplistic so either basis could work.

Also need to clarify what happens with the other records - would appear from your example that 'in' is already populated so you only want to populate the next record (ID=4) - what if record 6 was populated with 'in'? - there is no next record.

what happens if you subsequently edit record 3 and change the time to a later time? or remove or change the status? or change record 4 to an earlier time? or add a new record with a time between those of records 3 and 4?

Agree with June - this cannot be automated in a table, you will need to use a form and vba. What that vba looks like depends on clarifying what you are trying to do

I resolved it with VBA FindFirst, FindNext DAO Function