Creating a Monthly Lookup Table
Having a table where each record represents a single month can come in quite handy. Let's create such a table.
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
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:
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