sql - Using Start Date and End date in Access query

Question

Please can someone help me with this Access query that I am trying to create?

I have a table with a list of projects and their start date and end date. What I need is a query that shows the breakdown of project days by month for each project, for example:

PROJECT    Jan       Feb       Mar
AAAAA       7        28        2

...etc, where the numbers are the project days calculated based on the start date and end date. For example, in the above query, project AAAAA would have a start date of 24/01/2012 and end date of 02/03/2012

Please could someone guide me on how to achieve this?

Many thanks!!

Answer 1

I created a calendar table to make this easier to cope with. I included the code for the two procedures I used (CreateTable_calendar and LoadCalendar) below. I added a "work_day" field to the calendar table in case you want to limit the count of days to only your organization's work days in each month. If so, you will need to adjust the query's WHERE clause accordingly. And also reset the work_day values for each calendar date if my choice doesn't match yours.

Anyway, I'll leave those details for you to sort out. Without making an adjustment for work vs. non-work days, this query returns the result set I think you want.

TRANSFORM Count(sub.the_date) AS CountOfProjectDays
SELECT sub.Project_name
FROM
    (
        SELECT
            p.Project_name,
            MonthName(Month(c.the_date),-1) AS month_name,
            c.the_date
        FROM Projects AS p, tblCalendar AS c
        WHERE
            c.the_date >= [p].[start_date]
            And c.the_date <= [p].[end_date]
        ORDER BY p.Project_name
    ) AS sub
GROUP BY sub.Project_name
PIVOT sub.month_name
    In ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul",
       "Aug", "Sep", "Oct", "Nov", "Dec");

Notes:

1. I used the list of month names following PIVOT to force the order of the columns. Without that list, the columns would be presented alphabetically by month name. Shorten that list if you don't want/need columns for all 12 months.
2. This approach should work when all the dates are from a single calendar year. If you want to deal with a date range which spans more than a single year ... you've got more work to do. :-)

Make the calendar table:

Public Sub CreateTable_calendar()
    Const cstrTable As String = "tblCalendar"
    Dim cn As Object
    Dim strSql As String
    Set cn = CurrentProject.Connection

    On Error Resume Next
    cn.Execute "DROP TABLE " & cstrTable & ";"
    If Err.Number <> 0 Then
        Debug.Print Err.Description
    End If
    On Error GoTo 0

    strSql = "CREATE TABLE " & cstrTable & " (" & vbCrLf & _
        "the_date DATETIME CONSTRAINT pkey PRIMARY KEY," & vbCrLf & _
        "work_day YESNO," & vbCrLf & _
        "CONSTRAINT midnite_only CHECK " & _
        "(the_date = DateValue(the_date))" & vbCrLf & _
        ");"
    Debug.Print strSql
    cn.Execute strSql

    Set cn = Nothing
End Sub

Load the calendar table. Without giving it an argument for year, it will load all dates for the current year. Otherwise it loads the dates for the year you supply as the argument.

Public Sub LoadCalendar(Optional ByVal pYear As Integer)
    Const cstrTable As String = "tblCalendar"
    Dim db As DAO.Database
    Dim dte As Date
    Dim intYear As Integer
    Dim rs As DAO.Recordset
    Dim strMsg As String

On Error GoTo ErrorHandler

    intYear = IIf(pYear = 0, Year(Date), pYear)
    dte = DateSerial(intYear, 1, 1)

    Set db = CurrentDb
    Set rs = db.OpenRecordset(cstrTable, dbOpenTable, dbAppendOnly)

    Do While Year(dte) = intYear
        rs.AddNew
        rs!the_date = dte
        rs!work_day = Not (Weekday(dte) = vbSunday Or _
            Weekday(dte) = vbSaturday)
        rs.Update
        dte = dte + 1
    Loop
    rs.Close

ExitHere:
    On Error GoTo 0
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    strMsg = "Error " & Err.Number & " (" & Err.Description _
        & ") in procedure LoadCalendar"
    MsgBox strMsg
    GoTo ExitHere
End Sub

Edit: Calendar is a reserved word. See Problem names and reserved words in Access. I didn't notice that until I examined my database with Mr. Browne's Database Issue Checker Utility. So I changed the name calendar to tblCalendar in this answer. And I strongly recommend that utility. In addition to identifying problems with reserved words, it can inform you about many other potential problem issues.