如何每月自动计算MS Access DB 2016中的新字段(列) [英] How to automaticaly calculate a new field (Column) in MS Access DB 2016 each month

问题描述

我在win10平台上使用MS Access 2016.我在Access tbl_OB_PLAN_ALLT_DISTRIB中有一个表，该表具有一年中的月份作为标题示例:OCT，NOV，DEC等.

I'm using MS Access 2016 on a win10 platform. I have a table in Access tbl_OB_PLAN_ALLT_DISTRIB that has the months of the year as headers example: OCT, NOV, DEC, etc..

我有一个查询，该查询可以计算出所有资金汇入我的组织的月份总价值.

I have a query that calculates the total of the months dollar value for all my Organizations that money is sent to.

我需要帮助修改查询中的代码，以自动将下个月字段添加到该计算中.我目前必须每月查询一次并对其进行修改.

I am needing help modifying my code in my query to automatically add the next month field to that calculation. I currently have to go in the query and modify it every month.

这是我到目前为止正在使用的:

Here is what I am using so far:

OB_Plan: Nz([Oct],0)+Nz([NOV],0)+Nz([DEC],0)+Nz([JAN],0)

这是我的查询中的一个表达式.因此，我想一种自动在表达式中添加下个月的方法.例如:

This is placed as an expression in my query. So I would like a way to automatically add the next month in the expression. example:

OB_Plan: Nz([Oct],0)+Nz([NOV],0)+Nz([DEC],0)+Nz([JAN],0)+Nz([FEB],0)

我在想可能需要将此函数设为函数并在表达式中调用该函数.

I'm thinking I may need to make this a function and call the function in the expression.

任何想法都会有所帮助. SQL脚本已修改:

Any ideas would be helpful. SQL Script modified:

SELECT tbl_OB_Plan_ALLT_DISTRIB_Data.Record_ID, ([tbl_OB_Plan_ALLT_DISTRIB_Data]![Directorate] & " " & "(" & [tbl_OB_Plan_ALLT_DISTRIB_Data]![FUND_CENTER] & ")") AS Directorate, tbl_OB_Plan_ALLT_DISTRIB_Data.SAG, tbl_OB_Plan_ALLT_DISTRIB_Data.MDEP, Calc_OB_Plan([JAN],[FEB],[MAR],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC]) AS OB_Plan, [001-FINAL_INTEGRATION_TABLE]![AFP-FMEDDW] AS Current_AFP, [001-FINAL_INTEGRATION_TABLE]![ALLT-FMEDDW] AS Allotment_YTD, [001-FINAL_INTEGRATION_TABLE].TITLE
FROM tbl_OB_Plan_ALLT_DISTRIB_Data INNER JOIN [001-FINAL_INTEGRATION_TABLE] ON (tbl_OB_Plan_ALLT_DISTRIB_Data.MDEP = [001-FINAL_INTEGRATION_TABLE].MDEP) AND (tbl_OB_Plan_ALLT_DISTRIB_Data.SAG = [001-FINAL_INTEGRATION_TABLE].SAG)
GROUP BY tbl_OB_Plan_ALLT_DISTRIB_Data.Record_ID, ([tbl_OB_Plan_ALLT_DISTRIB_Data]![Directorate] & " " & "(" & [tbl_OB_Plan_ALLT_DISTRIB_Data]![FUND_CENTER] & ")"), tbl_OB_Plan_ALLT_DISTRIB_Data.SAG, tbl_OB_Plan_ALLT_DISTRIB_Data.MDEP, "", [001-FINAL_INTEGRATION_TABLE]![AFP-FMEDDW], [001-FINAL_INTEGRATION_TABLE]![ALLT-FMEDDW], [001-FINAL_INTEGRATION_TABLE].TITLE;

推荐答案

使用标准MS Access功能很难做到这一点的主要原因是因为您的数据库不遵守

The main reason that you are finding this difficult to achieve using standard MS Access functionality is because your database does not adhere to database normalisation rules.

当您发现自己将月份名称硬编码到查询表达式中并随后每个月修改查询结构的那一刻，这是数据库设计应更改的一个危险信号.

The moment that you find yourself hard-coding month names into query expressions and consequently modifying the structure of your queries each month is a red flag that your database design should change.

在您的情况下，根据您提供的信息，我建议定义表中的Month(最好是整数)或更好的一个Date字段，并显示每个月的数据表中的单独记录，而不是单个记录.

In your case, based on the information you have provided, I would suggest defining either Month (preferably as an integer) or, better yet, a Date field in your table, with the data for each month appearing as separate records in the table, rather than on a single record.

更改之后，选择变得很简单，因为您可以轻松地在查询的选择条件内计算希望选择的月份或日期范围，无需继续修改查询定义 >.

Following this change, selection becomes simple, as you can easily calculate the months or date range that you wish to select within the selection criteria of your queries, without the need to modify the query definition going forward.

没有这样的重新设计，解决方案将很丑陋，例如:

Without such redesign, the solutions will be ugly, for example:

select
    choose
    (
        month(date()),
        Nz([JAN],0),
        Nz([JAN],0)+Nz([FEB],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0)+Nz([MAY],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0)+Nz([MAY],0)+Nz([JUN],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0)+Nz([MAY],0)+Nz([JUN],0)+Nz([JUL],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0)+Nz([MAY],0)+Nz([JUN],0)+Nz([JUL],0)+Nz([AUG],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0)+Nz([MAY],0)+Nz([JUN],0)+Nz([JUL],0)+Nz([AUG],0)+Nz([SEP],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0)+Nz([MAY],0)+Nz([JUN],0)+Nz([JUL],0)+Nz([AUG],0)+Nz([SEP],0)+Nz([OCT],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0)+Nz([MAY],0)+Nz([JUN],0)+Nz([JUL],0)+Nz([AUG],0)+Nz([SEP],0)+Nz([OCT],0)+Nz([NOV],0),
        Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0)+Nz([MAY],0)+Nz([JUN],0)+Nz([JUL],0)+Nz([AUG],0)+Nz([SEP],0)+Nz([OCT],0)+Nz([NOV],0)+Nz([DEC],0)
    )
from
    tbl_OB_PLAN_ALLT_DISTRIB

或者:

select sum(t.amount)
from
(
    select 1 as mon, Nz([JAN],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 2 as mon, Nz([FEB],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 3 as mon, Nz([MAR],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 4 as mon, Nz([APR],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 5 as mon, Nz([MAY],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 6 as mon, Nz([JUN],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 7 as mon, Nz([JUL],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 8 as mon, Nz([AUG],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 9 as mon, Nz([SEP],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 10 as mon, Nz([OCT],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 11 as mon, Nz([NOV],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
    union
    select 12 as mon, Nz([DEC],0) as amount
    from tbl_OB_PLAN_ALLT_DISTRIB
) t
where 
    t.mon <= month(date())

或者，作为VBA功能:

Or, as a VBA function:

Function Calc_OB_Plan(vJan, vFeb, vMar, vApr, vMay, vJun, vJul, vAug, vSep, vOct, vNov, vDec) As Double
    Dim Arr()
    Arr = Array(vJan, vFeb, vMar, vApr, vMay, vJun, vJul, vAug, vSep, vOct, vNov, vDec)
    Dim i As Integer
    For i = 0 To Month(Date) - 1
        Calc_OB_Plan = Calc_OB_Plan + Nz(Arr(i), 0)
    Next i
End Function

可以通过以下方式从您的SQL查询中调用哪个:

Which could be called from your SQL query in the following way:

select Calc_OB_Plan([JAN],[FEB],[MAR],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC]) as OB_Plan
from tbl_OB_PLAN_ALLT_DISTRIB

由于按所有字段分组，没有任何聚合函数，因此可以用select distinct替换group by子句，以更有效地获得相同的结果，例如:

Since you are grouping by all fields, without any aggregation functions, you can replace the group by clause with a select distinct to obtain the same result more efficiently, e.g.:

SELECT DISTINCT
    tbl_OB_Plan_ALLT_DISTRIB_Data.Record_ID, 
    ([tbl_OB_Plan_ALLT_DISTRIB_Data]![Directorate] & " " & "(" & [tbl_OB_Plan_ALLT_DISTRIB_Data]![FUND_CENTER] & ")") AS Directorate, 
    tbl_OB_Plan_ALLT_DISTRIB_Data.SAG, 
    tbl_OB_Plan_ALLT_DISTRIB_Data.MDEP, 
    Calc_OB_Plan([JAN],[FEB],[MAR],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC]) AS OB_Plan, 
    [001-FINAL_INTEGRATION_TABLE]![AFP-FMEDDW] AS Current_AFP, 
    [001-FINAL_INTEGRATION_TABLE]![ALLT-FMEDDW] AS Allotment_YTD, 
    [001-FINAL_INTEGRATION_TABLE].TITLE
FROM 
    tbl_OB_Plan_ALLT_DISTRIB_Data INNER JOIN [001-FINAL_INTEGRATION_TABLE] ON
    tbl_OB_Plan_ALLT_DISTRIB_Data.MDEP = [001-FINAL_INTEGRATION_TABLE].MDEP AND 
    tbl_OB_Plan_ALLT_DISTRIB_Data.SAG = [001-FINAL_INTEGRATION_TABLE].SAG

这篇关于如何每月自动计算MS Access DB 2016中的新字段(列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！