Could you please help me create an update query in MS Access

[access2010]

Our branch is merging with another branch, our accounting systems are similar but slightly different and my boss would like to standardize the systems, before we upgrade to Office 365.
Could you please help me create an update query in MS Access 2003 for the existing data that would obtain?

Q1) the numeric calendar month number from the Date Transaction Field and place this number into the calendar month field.

Q2) The second query would obtain the Accounting_Year number from the Date_Transaction field and place this number into the Calendar_Year field.

Your Assistance will be appreciated.
Esther

 

[MarkK]

Don't do it. Ask your AI about the data principle SSoT.
You should never design and use a data structure in which a single data point, like a date, is split into component parts that co-exist with the single source they were split from. This invites the unforced error that your stored data is in conflict with itself.

Rather, store your data raw, and write a query that provides the calculated fields you need. Then, for any purpose that requires those calculated fields, don't use the table directly, use the query that provides those calculations.

Imagine a query like...

SELECT RowDate, SomeDataField, Year(RowDate) As RowYear, Month(RowDate) As RowMonth
FROM SomeTable

This is a very simple query. It solves what you are asking for in your post, which is: don't do it. And for fiscal year and quarters, again, calculate them in a query.
• A fiscal year is a fixed offset from a calendar year.
• The quarter is calculable using the DatePart() function.

hth