Could you please help me create an update query in MS Access (2 Viewers)

[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

 

[plog]

Check the users post history. Been coming here for years with this database and starting threads asking for hacks to issues that exist because of the improper table structure. First couple responses are how to properly structure the tables and correctly use foreign and primary keys. Then invariably someone gives them exactly what they requested and their poorly structured database limps on until the next thread.

At this point I don't blame them for not fixing things the right way.