MS-Access:查询以生成重复事件的日期? [英] MS-Access : Query to generate dates for recurring events?

问题描述

我对更复杂的查询不太熟悉，而且我在寻找一些示例以供分解使用时遇到了问题..

I'm not too familiar with the more complex queries and I'm having an issue finding some examples to take apart to utilize..

Event Name  | Due Date   | Frequency    |Frequency UOM
S-XYC-001   | 10/17/2020 |  360         |    D
S-XYA-003   | 6/1/2020   |  90          |    D
S-XYC-004   | 4/3/2020   |  180         |    D

因此，我们提供了一组工作，其中提供了初始截止日期、预期频率和测量单位(我已将所有频率转换为天，因为以前混合了天、季度、几个月……)

So, we have a set of work that has an initial due date provided, and an expected frequency along with a unit of measurement (I've converted all my frequencies into days as there was previously a mix of days, quarterlies, months..).

我被要求生成 2020 年事件的预测.因此，使用上面的源表，我们希望我们的列表如下所示:

I've been asked to generate a forecast of events in 2020. So, using the above source table we'd expect our list to look like this:

Event Name  | Due Date   | Frequency    | Frequency UOM
S-XYC-001   | 10/17/2020 |  360         |     D
S-XYA-003   | 6/1/2020   |  90          |     D
S-XYA-003   | 8/30/2020  |  90          |     D
S-XYA-003   | 11/28/2020 |  90          |     D
S-XYC-004   | 4/3/2020   |  180         |     D
S-XYC-004   | 9/30/2020  |  180         |     D

我真的不太确定要应用什么类型的查询来获得这些结果，或者老实说要寻找什么来获得一些我可以用来做我需要做的事情的例子.

I'm really not quite sure what type of query to apply to get these results, Or honestly what to look for to get some examples I can utilize to do what I need to.

推荐答案

首先，将频率更改为月份 - 分别为 12、3 和 6 - 以获得正确的结果日期.

First, change your frequency to months - 12, 3, and 6 respectively - to obtain the correct result dates.

然后，创建一个小查询并将其保存为十，因为它只返回 0 到 9:

Then, create a small query and save it as Ten as it simply returns 0 to 9:

SELECT DISTINCT 
    Abs([id] Mod 10) AS N
FROM 
    MSysObjects;

在这个通用查询中使用它，它能够在 DateTime 的整个范围内生成任何日期/时间序列:

Use that in this generic query that is capable of generating any sequence of date/time in the entire range of DateTime:

PARAMETERS 
    [Interval] Text ( 255 ), 
    [Number] IEEEDouble, 
    [Date] DateTime, 
    [Count] IEEEDouble;
SELECT 
    [Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000 AS Id, 
    DateAdd([Interval],Fix([Number])*([Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000),[Date]) AS [Date]
FROM 
    Ten AS Ten_0, 
    Ten AS Ten_1, 
    Ten AS Ten_2, 
    Ten AS Ten_3, 
    Ten AS Ten_4, 
    Ten AS Ten_5, 
    Ten AS Ten_6, 
    Ten AS Ten_7
WHERE 
    ((([Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000)<[Count]) 
    AND 
    (([Interval]) In ("s","n","h","d","w","ww","m","q","yyyy")) 
    AND 
    ((Fix([Number])*([Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000) Mod IIf(Fix([Number])=0,1,Fix([Number])))=0) 
    AND 
    ((Ten_0.N)<=[Count]\1) 
    AND 
    ((Ten_1.N)<=[Count]\10) 
    AND 
    ((Ten_2.N)<=[Count]\100) 
    AND 
    ((Ten_3.N)<=[Count]\1000) 
    AND 
    ((Ten_4.N)<=[Count]\10000) 
    AND 
    ((Ten_5.N)<=[Count]\100000) 
    AND 
    ((Ten_6.N)<=[Count]\1000000) 
    AND 
    ((Ten_7.N)<=[Count]\10000000));

另存为DatesSequence.

您可以使用参数运行:

Interval: "m"
Number: 3, 6, or 12
Date: Due Date
Count: The count of future event dates to list

结果:

您可以修改查询以过滤掉 2020 年之后的日期.

You could modify the query to filter out dates later than 2020.

这篇关于MS-Access:查询以生成重复事件的日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！