在Access查询中使用开始日期和结束日期 [英] Using Start Date and End date in Access query

问题描述

请有人帮助我解决我要创建的此Access查询吗?

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，其中数字是根据开始日期和结束日期计算的项目天数.例如，在上面的查询中，项目AAAAA的开始日期为24/01/2012，结束日期为02/03/2012

...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?

非常感谢！

推荐答案

我创建了一个日历表，以使其更易于处理.我在下面包括了我使用的两个过程(CreateTable_calendar和LoadCalendar)的代码.我想在日历表中添加一个"work_day"字段，以防您希望将天数限制为每个月的组织工作日.如果是这样，您将需要相应地调整查询的WHERE子句.如果我的选择不符合您的要求，还可以重置每个日历日期的work_day值.

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");

注释:

1. 我使用了PIVOT之后的月份名称列表来强制列的顺序.如果没有该列表，则这些列将按月份名称的字母顺序显示.如果您在12个月内都不需要/不需要列，请缩短该列表.
2. 当所有日期都来自一个日历年时，此方法应适用.如果要处理跨越一年以上的日期范围，则需要做更多的工作. :-)

制作日历表:

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

编辑:日历是保留字.请参见 Access中的问题名称和保留字.直到我使用Browne先生的数据库问题检查器实用程序检查了数据库.因此，在此答案中，我将日历名称更改为tblCalendar.我强烈推荐该实用程序.除了使用保留字识别问题外，它还可以告知您许多其他潜在的问题.

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.

这篇关于在Access查询中使用开始日期和结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！