SQL query works in SQL Server, fails in Excel (Microsoft Query)

Question

I have the following query which works as intended :

SELECT
        SERVICE_HISTORY.ServiceMode, SERVICE_HISTORY.CreatedDate,
        SERVICE_HISTORY.CreatedBy, SERVICE_HISTORY.Branch,
        SERVICE_HISTORY.Comments
FROM
        DEBA_US.dbo.SERVICE_HISTORY
JOIN 
        (SELECT MAX(SERVICE_HISTORY.CreatedDate) AS maxDate, CUSTOMER.AccNo
         FROM DEBA_US.dbo.CUSTOMER
         INNER JOIN (DEBA_US.dbo.SERVICE_HISTORY
                     INNER JOIN DEBA_US.dbo.CAR ON SERVICE_HISTORY.ROW_PK = CAR.ROW_PK) ON CUSTOMER.ROW_PK = CAR.ROW_PK
         WHERE
             CUSTOMER.AccNo LIKE 'CUS-1234'
             AND CAR.DateSubmitted IS NULL
         GROUP BY
             CUSTOMER.AccNo) AS testQuery ON testQuery.maxDate = SERVICE_HISTORY.CreatedDate

The query is to gives me the latest (max) service history date for a given customer.

When I execute the query in SQL Server, it works perfectly fine, but when I put the same query into EXCEL 2010 (Microsoft Query) it give me the error:

No Column name was specified for Column 1 of 'testQuery'
Invalid column name 'maxDate'
Statement could not be prepared

I'm not able to fix the query to get pass the error. Can someone please tell me why Excel isn't working with the above query? Thanks

Answer 1

You need to put testQuery and maxDate inside single quotations

SELECT
    SERVICE_HISTORY.ServiceMode, SERVICE_HISTORY.CreatedDate,
    SERVICE_HISTORY.CreatedBy, SERVICE_HISTORY.Branch,
    SERVICE_HISTORY.Comments
FROM
    DEBA_US.dbo.SERVICE_HISTORY
JOIN 
    (SELECT MAX(SERVICE_HISTORY.CreatedDate) AS 'maxDate', CUSTOMER.AccNo
     FROM DEBA_US.dbo.CUSTOMER
     INNER JOIN (DEBA_US.dbo.SERVICE_HISTORY
                 INNER JOIN DEBA_US.dbo.CAR ON SERVICE_HISTORY.ROW_PK = CAR.ROW_PK) ON     CUSTOMER.ROW_PK = CAR.ROW_PK
     WHERE
         CUSTOMER.AccNo LIKE 'CUS-1234'
         AND CAR.DateSubmitted IS NULL
     GROUP BY
         CUSTOMER.AccNo) AS 'testQuery' ON testQuery.maxDate =  SERVICE_HISTORY.CreatedDate