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

Suggest you look at crosstab queries: http://www.databasedev.co.uk/crosstab_queries.html

This shows exactly the kind of result you're looking for - counts per month. Note you may need to calculate days per month as an interim step eg by having a table per month to specify start and end dates you can then calculate how many days each project uses per month.

Your steps are something like:
1 table of date ranges per month
2 calculate start and end dates per project per month

Select p.projectName, md.monthNumber,
Case when p.startDate < md.startDate then md.startDate
When p.startDate < md.endDate then p.startDate else p.endDate
, case when p.endDate > md.endDate then md.endDate
When p.endDate > md.startDate then p.endDate else md.startdate end
From @projects p
Inner join @monthDates md on p.startDate <= md.endDate and p.endDate > md.startDate

In Access I think IIF is the equivalent to CASE?

You need to adjust the above depending on how you want to count the first /last days of a month (if a project finishes on the 31st does it count towards jan or does it need to continue to 1st feb etc) but its enough to get you started

Finally you use Access crosstab to transform this into the required format, doing date subtraction between the project start and end dates per month.

Answer 2

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.