Tutorial Blog 3 – Microsoft Access, Date Criteria

The Database Project with Access was a great exercise.  The project concepts build on top of what we learned or already knew in Excel and applied them within a new application.  There seemed to be a lot of confusion among peer students on this project, and I wanted to take it as an opportunity to explore one concept in particular.  The part of the query that required us to include only those members who “left” the gym during the prior 6 months from the assignment date seemed to cause a bit of unnecessary frustration, so I developed a video and this text to dig into that just a little more. 

First, it is important to impress that the functions within Access are the same as those within Excel.  Dates are counted in numbers of days, and the criteria for the lesson is very simple:  180 days less than the assignment due date, 3/27/2011, or more specifically, membership cancellations that occurred on or after September 30, 2010.  To demonstrate the similarities between Excel and Access formulas, I created these screen shots.  In math, there is more than one way to get an intended result.  I used the concept of counting days, so, simply put, I calculated the number of days in a month and multiplied by six to arrive at the number of days in the previous six months.  Then, I subtracted the result from the assignment due date (as required) to arrive at a cut-off date. 

In the above screen shot, the Assignment Due Date is hard-coded as 3/27/2011 since I don’t want the dates to change for grading purposes.  However, in order for the formula to be dynamic in a business environment, meaning it would update the prior six months over time, regardless of the Assignment Due Date, I would use the “Now” function:

Similarly, these functions are utilized within Access, only within the Expression Builder, which provides expanded functionality, rather than the Excel function bar:

The video I created walks through creating this expression within Access.

I hope that reading and viewing this tutorial blog proves to be helpful in some way. 
All material used in this tutorial is self-created by the author.