Move leave balance to next year in access database.

If you use a transaction method, you will not need an "annual" process.  Each transaction should add new leave or consume existing leave.  To find the current balance, you add all the transactions.  To add by year, you use a query with a date range.  The additional benefit of the transaction method is that it is easily auditable.  When you update a single record, you don't retain the detail necessary to justify the current balance.

Perhaps a tweaked version of the query already given above if you want to have an automated job/batch -

UPDATE Table1 AS A
       INNER JOIN Table1 AS B ON a.emp_number=b.emp_number
   SET a.no_of_days = b.no_of_days
WHERE a.currentyear= (select max(c.currentyear) from table1 c) and b.currentyear = a.currentyear-1;

1) Assuming that your automated job will run after the records for the current year are inserted into the table
2) Also we have to presume that it will update the leave balance of the previous year to the current max year available i,e if the job runs in 2022, it will update all 2022 records with the leave balance from 2021 provided you have a record for 2021 for such employees. If there is no record for previous year, then the inserted record will not get updated and it will stay with the same value as of the insert time
3) commit or save as well goes into your job
4) the table should not have records greater than the current year, for example - it cannot have 2020 records now as we are still in 2019

Thanks

Ths is teh table the above screenshot  is a query. I want to move all record from "1-1-2018   31-12-2018" to "1-1-2019   31-12-2019"

@MAS,
I hope you never get audited or you keep good paper records.  This design violates standard accounting practices.

Thank you. Our accounting system and HR System are different.
This is just to automate leave process.

People keep providing Update query solutions but my understanding of what you want to do is to copy last year and create records for the new year.  Therefore, you need an append query.  select the previous year's data, calculate the new leave balance, calculate the new dates and append the modified data.  Sorry, I can't bring myself to write the query for you.  I really hate to encourage poor design but it's not that hard.  Open the QBE and create a select query that selects the fields you want to copy (ignore the ID and the credited date since those will be generated or null in the appended record).  Change the query type to append and choose the target table.  In the AppendTo cell, either copy the column name or use a calculation such as DateAdd() to add 1 year to the two date fields.

Why don't you make an append query to select the data you want and just use dateAdd to add 1 year to the date fields.

Isn't that what I just said?

HOw to add dateadd() ?
I mean what is the command?

>>HOw to add dateadd() ?

It is an Access function:
https://support.office.com/en-us/article/dateadd-function-63befdf6-1ffa-4357-9424-61e8c57afc19

Not sure how to provide the SQL since you still haven't clarified how you want to do this.

As suggested, to generate the next year, you can insert into ohrm_leave_entitlement_test (select ...new_values from ohrm_leave_entitlement_test where year(from_date) = 2018)

You can hard-code the 2019 date or use dateadd to add 12 months to the from_date and to_date columns.

INSERT into yourtable(fld1, fld2, fld3)
Select fld1, DateAdd("y", 1, fld2), fld3
From yourtable
Where Year(fld2) = Forms!yourform!yeartocopy

@MAS

Is this still an active problem for you?