调用 Access 查询并为返回的每条记录添加列 [英] Call Access query and add column for each record returned

问题描述

我有一个看起来像这样的查询...

I've got a query that looks like so...

SELECT tbl1.ProjectID, tbl1.EntryDate AS StartDate, tbl2.EntryDate AS EndDate
FROM checklist_entries tbl1
INNER JOIN checklist_entries tbl2
ON tbl1.ProjectID = tbl2.ProjectID
WHERE tbl1.ChecklistDay = (SELECT ChecklistDayMin FROM milestone_def WHERE MilestoneDefID = [@milestoneID])
AND tbl2.ChecklistDay = (SELECT ChecklistDayMax FROM milestone_def WHERE MileStoneDefID = [@milestoneID])

如您所见，这是一个自联接表，它返回一个 ProjectID (FK)、一个 StartDate 和 EndDate，它们都是表中的 EntryDate 列.为了返回记录，我需要传入一个整数值 [@milestoneID]，它允许子查询返回 WHERE 子句所需的最小值和最大值.

As you can see, this is a self-joining table that returns me a ProjectID (FK), a StartDate and EndDate, both of which are the EntryDate column in the table. In order to have records returned, I need to pass in an integer value [@milestoneID] which allows the subqueries to return the min and max values needed for the WHERE clause.

由于此查询返回两个日期，我想了解如何在此查询的基础上进行构建并添加另一列，该列返回的整数值为 TotalDays(TotalDays 为工作日，周一至周五)根据返回的数据确定开始日期和结束日期.

Since this query returns two dates, I would like to find out how I could build upon this query and add another column that is returned with an integer value of TotalDays (TotalDays being business days, Mon-Fri) determined from the returned StartDate and EndDate.

我可以找到一个查询来返回天数，但基本上我想在 ACCESS 中创建一个查询，允许我调用此查询(并传入 [@MilestoneID] 参数)，并为返回的每条记录添加TotalDays 计数末尾的一列.

I can find a query to return the number of days but basically I would like to create a query in ACCESS that allows me to call this query (and pass in the [@MilestoneID] parameter) and for each record returned, add a column to the end of the TotalDays count.

推荐答案

类似于我的回答这里，你可以创建一个包含工作日数的 VBA 函数

Similar to my answer here, you can create a VBA function to include the number of weekdays using

SELECT 
    ... ,
    CountWeekdays(tbl1.EntryDate, tbl2.EntryDate) AS TotalWeekdays 
FROM ...

只需在 Access 中创建一个新的 Module 并将以下代码粘贴到其中:

Just create a new Module in Access and paste the following code into it:

Public Function CountWeekdays(Date1 As Date, Date2 As Date) As Long
Dim StartDate As Date, EndDate As Date, _
        Weekdays As Long, i As Long
If Date1 > Date2 Then
    StartDate = Date2
    EndDate = Date1
Else
    StartDate = Date1
    EndDate = Date2
End If
Weekdays = 0
For i = 0 To DateDiff("d", StartDate, EndDate)
    Select Case Weekday(DateAdd("d", i, StartDate))
        Case 1, 7
            ' weekend - do nothing
        Case Else
            Weekdays = Weekdays + 1
    End Select
Next
CountWeekdays = Weekdays
End Function

这篇关于调用 Access 查询并为返回的每条记录添加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！