Brent Ozar: Date Table Fan
Brent Ozar, legendary SQL Server guru, makes his case for using a "Date Table." The video is all done in T-SQL, but the concepts apply equally to ACE backends.
In an earlier article, I wrote about creating a monthly lookup table.
Former Access MVP Tom Wickerath pointed me to a Brent Ozar video where he extols the virtues of a daily calendar table (thanks, Tom!). Brent's a SQL Server guy, but his reasons for using a daily calendar table apply equally if you store your backend data in an Access file. Obviously, the table creation process itself will differ.
In the video, Brent lists four reasons for creating a date lookup table:
- Replace DATENAME [(Format in Access)] with quick and easy lookups
- Establish a source for fiscal periods
- Create date slices so all days/periods are reported
- Standardize period names and stop writing redundant strings
Most of the above reasons fall into the category of convenience. It's not particularly difficult to achieve the objectives of 1, 2, or 4 without a date table, but using the date table does make doing those things much easier.
The Killer Feature
The absolute killer feature of a date table is its ability to provide date placeholders when grouping date-based data that may have gaps in it. Jump to the 15:00 minute mark of the video to see Brent's demonstration of that particular feature.
For what it's worth, that feature is the main reason I first created a monthly lookup table in my own applications.
The Video
Why You Simply Must Have a Date Table
Cover image is a screen capture from Brent's video
