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.

 

[arnelgp]

as advised by the others, you do not need to save the period_month, period_year, accounting_month and accounting_year to the table.
you use query to show them (see qryInvestTrans2 query).

 

[Ken Sheridan]

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

The attached file illustrates the use of the functions in the following module. To return the accounting year for any date call the AcctYear function, passing the month and day of the month when the accounting year starts into the function as integer numbers, and the date for which the accounting year is to be returned as the third argument. If this argument is omitted the current date will be used. The accounting year is returned in the conventional format YYYY-YY, or YYYY if the accounting year is the same as the calendar year.

' basAccountingYear
Option Compare Database
Option Explicit

Public Function AcctYear(MonthStart As Integer, DayStart As Integer, Optional DateVal As Variant) As String

    Dim dtmYearStart As Date
   
    If IsMissing(DateVal) Or IsNull(DateVal) Then DateVal = VBA.Date

    If MonthStart = 1 And DayStart = 1 Then
        ' accounting year is calendar year, so return single year value
        AcctYear = Year(DateVal)
    Else
        ' get start of accounting year in year of date value
        dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)
       
        ' if date value is before start of accounting year
        ' accounting year starts year previous to date's year,
        ' otherwise it starts with date's year
        If DateVal < dtmYearStart Then
            AcctYear = Year(DateVal) - 1 & Format(Year(DateVal) Mod 100, "-00")
        Else
            AcctYear = Year(DateVal) & Format((Year(DateVal) + 1) Mod 100, "-00")
        End If
    End If

End Function

Public Function AcctYearQuarter(MonthStart As Integer, DayStart As Integer, Optional DateVal As Variant) As Integer

    Dim dtmYearStart As Date
   
    If IsMissing(DateVal) Or IsNull(DateVal) Then DateVal = VBA.Date

    If MonthStart = 1 And DayStart = 1 Then
        ' accounting year is calendar year, so call DatePart function
        AcctYearQuarter = DatePart("q", DateVal)
    Else
        ' get start of accounting year in year of date value
        dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)
       
        ' if date value is before start of accounting year
        ' accounting year starts year previous to date's year
        If DateVal < dtmYearStart Then
            dtmYearStart = DateAdd("yyyy", -1, dtmYearStart)
        End If
   
        ' get quarter for date in question
        Select Case DateVal
            Case Is >= DateAdd("m", 9, dtmYearStart)
            AcctYearQuarter = 4
            Case Is >= DateAdd("m", 6, dtmYearStart)
            AcctYearQuarter = 3
            Case Is >= DateAdd("m", 3, dtmYearStart)
            AcctYearQuarter = 2
            Case Else
            AcctYearQuarter = 1
        End Select
    End If
   
End Function

Public Function AcctYearStart(MonthStart As Integer, DayStart As Integer, Optional DateVal As Variant) As Date

    Dim dtmYearStart As Date
   
    If IsMissing(DateVal) Or IsNull(DateVal) Then DateVal = VBA.Date
   
    ' get start of accounting year in year of date value
    dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)
       
    ' if date value is before start of accounting year
    ' accounting year starts year previous to date's year,
    If DateVal < dtmYearStart Then
        dtmYearStart = DateAdd("yyyy", -1, dtmYearStart)
    End If

    AcctYearStart = dtmYearStart
   
End Function