在 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

...等，其中数字是根据开始日期和结束日期计算的项目天数.例如，在上面的查询中，项目 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屋！