Subquery in MS Query doesn't work

Question

I'm a beginner in SQL. I hope my question is not too stupid.

I'm trying to write a query in MS Query and want to see the results in Excel. The problem is that MS Query doesn't accept my code. I get the message:

table '(select' couldn't be added.

Here's the code:

SELECT  
    T1.Jahr, 
        T1.Monat, 
        T1.Land_ID, 
        T1.Kanal, 
        T1.Items_per_HC, 
        T1.Planstand, 
        T1.Zeitraum,
        T2.Zeitraum,
        T2.LAND_ID,
        T2.KANAL,
        T2.IST_ITEMS_PER_HC_EXCL_EASY 
FROM 
(SELECT tPLAN.Jahr, tPLAN.Monat, tPLAN.Land_ID, tPLAN.Kanal, 
        tPLAN.Items_per_HC, tPLAN.Planstand, tZ.Zeitraum
    FROM t_Plan_Items_per_HC tPLAN, t_PLAN_ZEITRAUM tZ
    WHERE tPLAN.Jahr = tZ.Jahr 
    AND tPLAN.Monat = tZ.Monat) as T1
INNER JOIN a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC AS T2 
  ON (T1.Zeitraum=T2.Zeitraum 
  AND T1.Land_ID=T2.LAND_ID 
  AND T1.Kanal=T2.KANAL)

What is wrong?

I have to see the content of table t_Plan_Items_per_HC and table a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC in only one table. They should be joined by Land_ID, Zeitraum, and KANAL.

UPDATE: Now I tried the same code in SQL Developer and it works. It seems to be an MS Query-specific problem. Unfortunately I have to solve this problem in MS Query so I still need help.

Answer 1

Your query can be simplified (regardless of SQL flavor) not to use an inner query (subselect). This is not tested, but it should be closer to what you need:

SELECT tPLAN.Jahr, tPLAN.Monat, tPLAN.Land_ID, tPLAN.Kanal, T1.tPLAN, tPLAN.Planstand, 
    tZ.Zeitraum, T2.IST_ITEMS_PER_HC_EXCL_EASY 
FROM t_Plan_Items_per_HC tPLAN 
    JOIN t_PLAN_ZEITRAUM tZ ON tZ.Jahr = tPLAN.Jahr AND tZ.Monat = tPLAN.Monat
    JOIN a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC AS T2 
        ON (T2.Zeitraum=tZ.Zeitraum AND T2.Land_ID=tPLAN.LAND_ID AND T2.Kanal=tPLAN.KANAL) 

Since all JOINs are INNER, you can just select from one of the tables and JOIN with each of the others using your criteria. Also, WHERE conditions like:

FROM t_Plan_Items_per_HC tPLAN, t_PLAN_ZEITRAUM tZ
    WHERE tPLAN.Jahr = tZ.Jahr 
    AND tPLAN.Monat = tZ.Monat) 

are not recommended. Use JOINs instead.