Creating a Monthly Lookup Table

Mike Wolfe -

Calendar tables, where each record represents a single date, have a variety of uses:

  • Data is relatively easy to generate and requires little maintenance once created.
  • Calendar data can be used to service any reports that require it, removing the need to recreate it in each report.
  • We can implement a large number of calendar metrics, and can easily add more as needed.
  • Calendar data is tiny in terms of space used. Performance against this data is generally quite fast.
  • Complex reports can be simplified by removing commonly used DATEPART and DATEADD computations.
  • Important business logic, such as holidays, can be centralized and maintained in a single location.
  • Maintaining calendar data in a single table ensures we do not encounter inconsistencies between different reports, reporting systems, or applications that need it.

(source: Designing a Calendar Table)

Monthly Calendar Tables

I've never used a calendar table like the ones described in the link above where each record represents a single date.  I have, however, created monthly calendar tables where each record represents a single month.

Most of the advantages of a daily calendar table also apply to monthly calendar tables.  I find they also offer the following additional benefits:

  • Simplifies the creation of grouping queries
  • Supports looping by month in code
  • Helps with the creation of crosstab queries

I'll explore some of those benefits more in-depth in future articles.  For now, let's discuss how to create such a table.

Creating a Monthly Calendar Table in ACE

Where you create the calendar table depends on how you will be using it.  Generally speaking, you should create it in the same database as the tables to which you will be joining it.  For simplicity, I will demonstrate how to use VBA to create the table in the local database file:

Sub CreateYrMoStructure()
    Dim s As String
    
    'Create the table with MoStart as the primary key
    s = s & "CREATE TABLE YrMo " & vbNewLine
    s = s & "(MoStart DATETIME CONSTRAINT PriKey PRIMARY KEY" & vbNewLine
    s = s & ",MoEnd DATETIME NOT NULL" & vbNewLine
    s = s & ",Yr SHORT NOT NULL" & vbNewLine
    s = s & ",Mo BYTE NOT NULL" & vbNewLine
    s = s & ",DaysInMo BYTE NOT NULL)"
    CurrentDb.Execute s, dbFailOnError
    
    'Create a unique index on the Yr and Mo field combination
    s = "CREATE UNIQUE INDEX YearMonth ON YrMo (Yr ASC, Mo ASC)"
    CurrentDb.Execute s, dbFailOnError
        
End Sub

Note: The s = s & "..." & vbNewLine boilerplate is added via a Notepad++ macro.

The table I created above has the following five fields:

  • MoStart : the start of the month
  • MoEnd   : the end of the month
  • Yr      : the year
  • Mo      : the month
  • DaysInMo: number of days in the month

You should add or remove fields to the table to suit your situation.  

Abbreviations and code readability

I use the abbreviations "Yr" and "Mo" in place of year and month.  I normally use longer names in my code and field names.  Longer names usually enhance readability.  However, I'm breaking my rule in this case for a few reasons.  

First, "Year" and "Month" are VBA function names, so they are inappropriate field names.  Second, they reflect a consistent naming convention within our company's codebase.  Third, their conciseness results in a strong signal to noise ratio.  

Note that this last point is only true for these particular two-character identifiers because of how common they are in our company's code and database structures.  Those same abbreviations would be a terrible choice for you if they conflict with your own naming convention.  Adjust accordingly.

Populating the Monthly Calendar Table

Now that we have the YrMo structure in place, let's populate it.  You will want to customize the starting and ending years to suit your own situation.

'Populate the YrMo monthly calendar table
Sub PopulateYrMoTable()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("YrMo", dbOpenDynaset, dbAppendOnly)
    
    Dim Yr As Integer, Mo As Byte
    For Yr = 1991 To 2099
        For Mo = 1 To 12
            With rs
                .AddNew
                .Fields("MoStart").Value = DateSerial(Yr, Mo, 1)
                .Fields("MoEnd").Value = DateSerial(Yr, Mo + 1, 0) + _
                                         TimeSerial(23, 59, 59)
                .Fields("Yr").Value = Yr
                .Fields("Mo").Value = Mo
                .Fields("DaysInMo").Value = Day(DateSerial(Yr, Mo + 1, 0))
                .Update
            End With
        Next Mo
    Next Yr
    Debug.Print "Done building YrMo table"
End Sub

If you run the CreateYrMoStructure() and PopulateYrMoTable() routines, you should get a table that looks like this:

The YrMo monthly calendar table

Note that for the MoEnd column I set the time to one second prior to midnight. That's an important detail.  Without the time portion it would be easy to accidentally create a grouping query that excludes records from the last day of the month if those records have a time portion.  And, since there is no date-only datatype in ACE, you can never be sure that future data won't include a time portion where once there was none.

Image by Hans Braxmeier from Pixabay