联合查询"unable to execute query, invalid operation or syntax using multi-value field"

Question

我正在尝试运行下面的查询，但得到：

无法使用多值字段执行查询、无效操作或语法

此处的所有列都匹配，并且所有值的数据类型相同。似乎无法在网上找到我理解的信息。有人可以帮忙吗？

SELECT
    fq.fk_spEngineerID,
    fq.spQuoteID,
    fq.spOrderID,
    fq.BU,
    fq.fk_spProductTypeID,
    fq.fk_spCompanyID,
    fq.fk_spOfficeID
FROM (
    SELECT
        fk_spEngineerID,
        spQuoteID,
        NULL AS spOrderID,
        BU,
        fk_spProductTypeID,
        fk_spCompanyID,
        fk_spOfficeID
    FROM FactQuote
    UNION ALL
    SELECT
        fk_spEngineerID,
        NULL AS spQuoteID,
        spOrderID,
        BU,
        fk_spProductTypeID,
        fk_spCompanyID,
        fk_spOfficeID
    FROM FactOrder
) AS fq
GROUP BY
    fq.fk_spEngineerID,
    fq.spQuoteID,
    fq.spOrderID,
    fq.BU,
    fq.fk_spProductTypeID,
    fq.fk_spCompanyID,
    fq.fk_spOfficeID;

此查询运行差异是数据源是查询而不是表

select 
    fq.fk_spEngineerID, 
    fq.spQuoteID,
    fq.spOrderID, 
    fq.BU,
    fq.spProductTypeID, 
    fq.fk_spCompanyID, 
    fq.fk_spOfficeID
From (
    Select 
        fk_spEngineerID, 
        spQuoteID,
        "" as spOrderID, 
        BU, 
        spProductTypeID, 
        fk_spCompanyID, 
        fk_spOfficeID, 
    From QuotestatsSubQuery 
    Union All 
        Select 
        fk_spEngineerID,
        "" as spQuoteID,spOrderID,
         BU, 
        spProductTypeID, 
        fk_spCompanyID, 
        fk_spOfficeID 
    From OrderstatsSubQuery
) as fq 
Group By  
    fq.fk_spEngineerID,
    fq.spQuoteID,
    fq.spOrderID,  
    fq.BU, 
    fq.spProductTypeID, 
    fq.fk_spCompanyID, 
    fq.fk_spOfficeID;

Answer 1

这太长了，无法发表评论。

MS Access 不支持子查询中的UNION或UNION ALL。 在我看来，这是一个严重的限制。

除了迁移到另一个数据库之外，您所能做的就是定义一个视图。 视图可以包含表的UNION ALL，然后可以在聚合查询中使用该UNION。