代码之家 › 专栏 › 技术社区 › Daniel Fersbeanto

MS Access-使用fifo逻辑将2个视图连接到结果表中

ms-access-2007 sql

Daniel Fersbeanto · 技术社区 · 10 年前

我有一个这样的视图(PurchaseOrder视图)

PONo        Date        ProductId    Price   Amount 
PO 00001    01/07/2014  AS15         4.750   10.000 
PO 00002    02/07/2014  AS15         4.000   100.000 
PO 00003    05/07/2014  AS24         4.500   30.000 
PO 00004    06/07/2014  AS19         4.250   80.000 
PO 00005    08/07/2014  AS19         4.500   25.000 
PO 00006    09/07/2014  AS15         4.750   15.000 
PO 00007    11/07/2014  AS19         4.250   75.000 
PO 00008    12/07/2014  AS24         4.000   95.000

和销售概要视图(SalesSummary view)

ProductId    AmountSale 
AS15         35.000 
AS19         88.000 
AS24         50.000

我需要这样的标签/视图

PONo        Date        ProductId    Price   Amount      AmountSale 
PO 00001    01/07/2014  AS15         4.750   10.000      10.000 
PO 00002    02/07/2014  AS15         4.000   100.000     25.000 
PO 00003    05/07/2014  AS24         4.500   30.000      30.000 
PO 00004    06/07/2014  AS19         4.250   80.000      80.000 
PO 00005    08/07/2014  AS19         4.500   25.000      8.000 
PO 00006    09/07/2014  AS15         4.750   15.000      - 
PO 00007    11/07/2014  AS19         4.250   75.000      - 
PO 00008    12/07/2014  AS24         4.000   95.000      20.000

前5个字段与PurchaseOrderView相同。
棘手的部分是如何获得AmountSale 来自SalesSummary

1 回复 | 直到 10 年前

VBlades 10 年前

如果找不到SQL Server解决方案,可以在Access中执行此操作。将此函数添加到公共模块:

编辑: 当AmountSales未完全覆盖给定ProductID的第一个PO时,要考虑的代码已更改。

编辑2: 注意到逻辑错误并修复了它。

Public Function ReturnAmountSale(strPoNo As String, strProductID As String, curAmount As Currency, curAmountSale As Currency) As Variant

    Dim curAmountSaleUpToCurrentPO As Currency
    Dim varAmountSalePriorToCurrentPO As Variant

    'Get the total Amount for the current ProductID up to and including given PO.
    curAmountSaleUpToCurrentPO = DSum("Amount", "PurchaseOrder", "[PoNo] <= '" & strPoNo & "' AND [ProductID] = '" & strProductID & "'")

    'If there is enough in SalesAmount to cover the whole cost, return the whole Amount.
    If curAmountSale - curAmountSaleUpToCurrentPO >= 0 Then
        ReturnAmountSale = Format(curAmount, "0.00")
    Else
        'Get the the total Amount in ProductID prior to current PO.
        varAmountSalePriorToCurrentPO = DSum("Amount", "PurchaseOrder", "[PoNo] < '" & strPoNo & "' AND [ProductID] = '" & strProductID & "'")

        'If current PO is first in ProductID, varAmountSalePriorToCurrentPO will be null;
        'determine covered amount.
        If IsNull(varAmountSalePriorToCurrentPO) = True Then
            If curAmount <= curAmountSale Then
                ReturnAmountSale = Format(curAmount, "0.00")
            Else
                ReturnAmountSale = Format(curAmountSale, "0.00")
            End If
        Else
            'If current PO is not first in ProductID, varAmountSalePriorToCurrentPO
            'will have a value; determine the covered amount.
            varAmountSalePriorToCurrentPO = curAmountSale - varAmountSalePriorToCurrentPO

            If varAmountSalePriorToCurrentPO <= 0 Then
                ReturnAmountSale = Null
            Else
                ReturnAmountSale = Format(varAmountSalePriorToCurrentPO, "0.00")
            End If
        End If
    End If

End Function

然后将此列添加到查询中(在查询中的现有表/查询中,不能有同名的列,因此下划线也是如此):

Amount_Sale: ReturnAmountSale([PurchaseOrder].[PoNo],[PurchaseOrder].[ProductID],[PurchaseOrder].[Amount],[SalesSummary].[AmountSale])

注:这假设采购订单是按照每个产品ID的日期顺序连续发布的。