Let’s say you are starting a new five-year project on July 1, but your client is going to expect performance reports across three reporting timelines: the calendar year (January–December), the fiscal year (October–September), and the project year (July–June). Initially, it may seem practical to require that all staff indicate this manually during data entry, as shown in the electronic form to the right. However, that would require a lot of additional effort on the data entry side and leave you highly vulnerable to mistakes.
Another option would be to write a calculation to automatically determine each reporting period based on the dates entered on the form. This is certainly possible using logical tests and would reduce the data entry burden; however, you would need to ensure you replicate the same calculations on every form in your database. That causes complications later if you need to modify the reporting periods, such as when a project gets extended.
A simpler way to reconcile these reporting periods is to create a central date reference table in your database. This is a single table that includes a list of all the dates during which your project is active and additional columns that tag each date into the appropriate reporting timeline. Once created, you can use the “Date Check” field to connect it to any other data table in your system by joining on any field that is a date type. The basic structure will look like this:
This date reference table can now be used for all forms and datasets in your database. And, if the reporting periods require any changes, you only need to modify this central table. In addition to dynamically reconciling reports across years, you can also integrate tags for additional reporting periods (e.g., semi-annually and quarterly) to further simplify tracking. Examples of the query structure using Microsoft Access and structured query language, or SQL, and snapshots of the query output tables are below.
If you prefer to run it through an SQL query, this is how the example above would appear for project year:
SELECT [Date Reference].[Project Year], COUNT([Technical Assistance].[Beneficiary Assisted]) FROM [Technical Assistance] INNER JOIN [Date Reference] ON [Technical Assistance].[Date of Assistance]=[Date Reference].[Date Check] GROUP BY [Date Reference].[Project Year];
Along with saving time on data entry and calculations for reporting periods, the date reference approach also proves extremely useful if you need to track participation trends across reporting periods (e.g., new, continuing, dropout), eliminate double counting, or calculate participation across multiple project activities.
View all blogs in the Data Digest series
Comments
