vba 将日期范围拆分为单个记录，保留 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20790681/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split range of dates into individual records, keeping IDs
提问by AnalyzeThis
I am working with an Access 2010 db that has a table with associate names with a range of dates that the employee has requested off, formatted as follows:
我正在使用一个 Access 2010 db，它有一个表，其中的关联名称包含员工要求关闭的日期范围，格式如下：
Table DateRanges
桌子 DateRanges
Name--| StartDate | EndDate
Joe---| 12/20/13 | 12/22/13
Sue---| 12/24/13 | 12/26/13
Guy---| 12/30/13 | 01/02/14
I'm trying to make a query that will change the data to have an individual record for each date, but also keep the appropriate name with each record. Like this:
我正在尝试进行一个查询，该查询将更改数据以在每个日期拥有单独的记录，但同时为每条记录保留适当的名称。像这样：
Table Separated
桌子 Separated
Name -- DatesOff
Joe ----- 12/20/13
Joe ----- 12/21/13
Joe ----- 12/22/13
Sue ----- 12/24/13
Sue ----- 12/26/13
Guy ----- 12/30/13
................
Guy ----- 01/02/13
I've seen a few different examples here regarding splitting just date ranges, but not bringing along an employee name/id with it. Any ideas? I'm open to using SQL or VBA to get it done.
我在这里看到了几个不同的例子，它们只拆分日期范围，但没有附带员工姓名/ID。有任何想法吗？我愿意使用 SQL 或 VBA 来完成它。
回答by
You can create a table that contains each day and join it to your DateRangestable like so:
您可以创建一个包含每一天的表格并将其加入您的DateRanges表格，如下所示：
SELECT
dr.Name,
d.DateValue AS DatesOff
FROM
Days d
INNER JOIN
DateRanges dr
ON (d.DateValue BETWEEN dr.StartDate AND dr.EndDate)
