如何通过SQL自动将月度记录插入表中? [英] How do I Automatically insert monthly records into a table via SQL?

问题描述

我正在尝试根据另一张表中的指令在一个表中生成月度记录.软件-MS Access 2007，尽管我正在这里寻找SQL解决方案.为了大大简化此过程，下面对表进行说明:

I'm trying to generate monthly records in one table based on instructions in another table. Software - MS Access 2007, though I'm looking for an SQL solution here. To greatly simplify the matter, let's say the following describes the tables:

 TaskManager:
  - DayDue
  - TaskName

 Task:
  - DateDue
  - TaskName

因此，发生的情况是TaskManager {15，应付帐款"}中可能有一个条目，因此这将导致Task表中的应付帐款"记录，到期日为每月15号.我希望它创建过去几个月和下一年的记录.

So what happens is that there may be an entry in TaskManager {15, "Accounts due"}, so this should lead to an "Account due" record in the Task table with the due date being the 15th of each month. I'd want it to create records for the last few months and the next year.

我想做的是首先创建一个SELECT查询，该查询为TaskManager表中的每个记录生成x条记录，并带有每个月的日期.之后，我执行一个INSERT查询，如果记录在上述SELECT查询中不存在，则会将记录插入Task表中.

What I'm thinking that I need to do is first create a SELECT query that results in x records for each record in the TaskManager table, with a date for each month. After that, I do an INSERT query which inserts records into the Task table if they do not EXIST in the aforementioned SELECT query.

我想可以管理INSERT查询，尽管我在弄清楚如何执行SELECT查询时遇到了麻烦.有人可以给我指点吗?

I think I can manage the INSERT query, though I'm having trouble figuring out how to do the SELECT query. Could someone give me a pointer?

推荐答案

这是我使用Remou的Calendar表想法开发的解决方案.

Here is the solution I developed using Remou's Calendar table idea.

首先创建一个Calendar表，该表仅包含所需范围内的所有日期.只需在Excel中创建日期并将其粘贴到表格中即可.这也是一种非常可靠的方法，因为Excel可以正确处理现代日期范围内的leap年.

First create a Calendar table, which simply contains all dates for a desired range. It's easy to just make the dates in Excel and paste them into the table. This is also a very reliable way of doing it, as Excel handles leap years correctly for the modern range of dates.

构建此表后，将运行三个查询.第一个是SELECT，它根据日期和频率选择TaskManager生成的每个可能的任务.该查询称为TaskManagerQryAllOptions，并具有以下代码:

After building this table, there are three queries to run. The first is a SELECT, which selects every possible task generated by the TaskManager based on the date and frequency. This query is called TaskManagerQryAllOptions, and has the following code:

SELECT TaskManager.ID, Calendar.CalendarDate
FROM TaskManager INNER JOIN Calendar ON
        TaskManager.DateDay = Day(Calendar.CalendarDate)
WHERE (TaskManager.Frequency = "Monthly")
    OR (TaskManager.Frequency = "Yearly" AND
            TaskManager.DateMonth = Month(Calendar.CalendarDate))
    OR (TaskManager.Frequency = "Quarterly" AND
            (((Month(Calendar.CalendarDate)- TaskManager.DateMonth) Mod 3) =  0));

以上大部分内容是针对每个季度的日和月"对可能涵盖的不同选项.下一步是另一个SELECT查询，该查询从TaskManagerQryAllOptions中选择日期在所需范围内的记录.该查询称为TaskManagerQrySelect.

The bulk of the above is to cover the different options a quarterly Day and Month pair could cover. The next step is another SELECT query, which selects records from the TaskManagerQryAllOptions in which the date is within the required range. This query is called TaskManagerQrySelect.

SELECT TaskManagerQryAllOptions.ID, TaskManager.TaskName,
        TaskManagerQryAllOptions.CalendarDate
FROM TaskManagerQryAllOptions INNER JOIN TaskManager
        ON TaskManagerQryAllOptions.ID = TaskManager.ID
WHERE (TaskManagerQryAllOptions.CalendarDate > Date()-60)
    AND (TaskManagerQryAllOptions.CalendarDate < Date()+370)
    AND (TaskManagerQryAllOptions.CalendarDate >= TaskManager.Start)
    AND ((TaskManagerQryAllOptions.CalendarDate <= TaskManager.Finish) 
            OR (TaskManager.Finish Is Null))
ORDER BY TaskManagerQryAllOptions.CalendarDate;

最后一个查询是INSERT.由于我们将经常使用此查询，因此我们不希望它生成重复项，因此我们需要过滤掉已创建的记录.

The final query is an INSERT. As we will be using this query frequently, we don't want it to generate duplicates, so we need to filter out already created records.

INSERT INTO Task ( TaskName, TaskDate )
SELECT TaskManagerQrySelect.TaskName, TaskManagerQrySelect.CalendarDate
FROM TaskManagerQrySelect
WHERE Not Exists(
    SELECT *
    FROM Task
    WHERE Task.TaskName = TaskManagerQrySelect.TaskName
        AND Task.TaskDate = TaskManagerQrySelect.CalendarDate);

此方法的局限性在于，如果更改重复日期(例如每月的15号)，则将保留错误日期的将来记录.解决方案是用调整后的日期更新所有将来的记录，然后运行插入.

One limitation of this method is that if the date of repetition (e.g. the 15th of each month) is changed, the future records with the wrong day will remain. A solution to this would be to update all the future records with the adjusted date, then run the insert.

这篇关于如何通过SQL自动将月度记录插入表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！