代码之家 › 专栏 › 技术社区 › Adnan Mehmood

如何将这两个不同的SQL查询作为源放在一个报表中。我想删除查询并将此sql用作Access Report的源

ms-access-2007 ms-access-2010 vba ms-access sql-server

-2

Adnan Mehmood · 技术社区 · 8 年前

    SELECT
        [Ledger Entries JC].[Object Code],
        DLookUp("Description",  " ACTVTY and CMDTY Descriptions", "L4='" & [Object Code] & "'") AS [Object Code Description],
        Left([Ledger Entries JC]![Subsidiary], 2) AS L4,
        DLookUp("Description", "ACTVTY and CMDTY Descriptions", "L4='" & [L4] & "'") AS [L4 Description],
        Left([Ledger Entries JC]![Subsidiary], 3) AS L5,
        DLookUp("Description", "ACTVTY and CMDTY Descriptions", "L4='" & [L5] & "'") AS [L5 Description],
        Left([Ledger Entries JC]![Subsidiary], 4) AS L6,
 DLookUp("Description", "ACTVTY and CMDTY Descriptions", "L4='" & [L6] & "'") AS [L6 Description],
        Right([Ledger Entries JC]![Subsidiary], 4) AS L7,
        DLookUp("Description", "ACTVTY and CMDTY Descriptions", "L4='" & [L7] & "'") AS [L7 Description],
        [Ledger Entries JC].Amount,
      DLookUp("Budget", "Budget By Activity By Commodity", "WBS='" & [WBS] & "'") AS Budget,
        [L6] & "." & [Object Code] & "." & [L7] AS WBS,

 [Ledger Entries JC].Date
    FROM
        [Ledger Entries JC]



  UNION
        Transform
        Sum([Budget By Activity By Commodity].Budget) As [Total Of Budget]
    SELECT
        [Budget By Activity By Commodity].L4,
        [Budget By Activity By Commodity].[L4 Description],
        [Budget By Activity By Commodity].Budget AS [Total Budget],
        [Budget By Activity By Commodity].L5,
        [Budget By Activity By Commodity].[L5 Description],
        [Budget By Activity By Commodity].L6,
        [Budget By Activity By Commodity].[L6 Description],
        [Budget By Activity By Commodity].L7,
        [Budget By Activity By Commodity].WBS AS WBS,
        [Budget By Activity By Commodity].[WBS Description],
        [Ledger Entries JC Qry].WBS AS [WBS 2],
        Sum([Ledger Entries JC Qry].Amount) As Amount
    FROM
        [Ledger Entries JC Qry]
            RIGHT JOIN [Budget By Activity By Commodity]
            ON [Ledger Entries JC Qry].WBS = [Budget By Activity By Commodity].WBS
    Group BY
        [Budget By Activity By Commodity].L4,
        [Budget By Activity By Commodity].[L4 Description],
        [Budget By Activity By Commodity].Budget,
        [Budget By Activity By Commodity].L5,
        [Budget By Activity By Commodity].[L5 Description],
        [Budget By Activity By Commodity].L6,
        [Budget By Activity By Commodity].[L6 Description],
        [Budget By Activity By Commodity].L7,
        [Budget By Activity By Commodity].WBS,
        [Budget By Activity By Commodity].[WBS Description],
        [Ledger Entries JC Qry].WBS
    PIVOT
        [Budget By Activity By Commodity].[Object Code] In(57523, 57531, 57532, 57533, 57535, 57537, 57540, 57590, 57626, 57627, 57628, 57629, 57630, 58230)

1 回复 | 直到 8 年前

Gustav 8 年前

只需将SQL复制并粘贴到报表的RecordSource属性中。

也就是说,它可能会被优化很多。