CODEWITHSUNDEEP
sqlms-access
M_kul
M_kul

Reputation: 173

Union query "unable to execute query, invalid operation or syntax using multi-value field"

I am trying to run the query below and I'm getting:

unable to execute query, invalid operation or syntax using multi-value field

All columns here are matched up and all the values are of same data type. Can't seem to find info on this online that I understand. Can someone help?

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;

This query runs difference being the source of data is queries not tables

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;

Upvotes: 0

Views: 898

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This is too long for a comment.

MS Access does not support UNION or UNION ALL in subqueries. In my opinion, this is a severe limitation.

Apart from migrating to another database, all you can do is define a view. The view can contain the UNION ALL of the tables, and you can then use the UNION in an aggregation query.

Upvotes: 1

Related Questions