Ms Access SQL Query syntax error

Question

the following is my ms access sql query

    SELECT '2012/8'      AS [Period],
       yearmonth.[Monthno (PS)] AS monthno,
       [assignment_2012].[cluster code] AS cluster,
       'Transfer out' AS Remark,
       -1*Count(*)      AS [number],
       '5'           AS [sorting]
FROM  [assignment_2012] 
INNER JOIN namesort ON [assignment_2012].post = namesort.post 
INNER JOIN yearmonth ON [assignment_2012].[year month]=yearmonth.[Year Month (HM)] 
INNER JOIN
       (SELECT empid
                     FROM   (SELECT empid,
                                    [cluster],
                                    Count(*) AS [empid no]
                             FROM   (SELECT [ID] as empid,
                                            [Cluster Code] as cluster 
                                     FROM   [assignment_2012]
                                            INNER JOIN yearmonth
                                                    ON [assignment_2012].[year month]
                                                       =
                                                       yearmonth.[Year Month (HM)]
                                     WHERE  yearmonth.[Monthno (PS)] = 243
                                            AND [assignment_2012].hc_adj = 1
                                            AND [assignment_2012].term <> 'Temporary'
                                     UNION
                                     SELECT empid,
                                            [cluster]
                                     FROM   [2012]
                                     WHERE  monthno = 244
                                            AND [2012].term <> 'Temporary'
                                            AND ( div <> 'XXX'
                                                   OR div IS NULL )
                                            AND hc = 1)
                             GROUP  BY [empid],
                                       [cluster]
                             HAVING Count(*) = 1)
                     GROUP  BY empid
                     HAVING Count(*) > 1) As Emps
ON [assignment_2012]].EmpID = Emps.EmpID
WHERE  yearmonth.[Monthno (PS)] = 243
       AND [assignment_2012].hc_adj = 1
       AND yearmonth.[Monthno (PS)] BETWEEN namesort.monthno_start AND namesort.monthno_end
       AND [assignment_2012].term <> 'Temporary'
GROUP  BY yearmonth.[Monthno (PS)], [assignment_2012].[cluster code]

When i run the query, it prompt message that "syntax error (missing operator) in query expression '[assignment_2012].post = namesort.post INNER JOING........"

i found that it return error if it include "INNER JOIN yearmonth ON [assignment_2012].[year month]=yearmonth.[Year Month (HM)]" in the sql statement

Have you tried to remove the different selections one by one to locate precisely where the error is? — bAN
– bAN, Commented Dec 27, 2012 at 8:06
yes, i had tried it. i found that it return error if it include "INNER JOIN yearmonth ON [assignment_2012].[year month]=yearmonth.[Year Month (HM)]" in the sql statement — Joe Yan
– Joe Yan, Commented Dec 27, 2012 at 8:11

Joe Yan · Accepted Answer · 2012-12-27 08:27:03Z

i had revised the INNER JOIN statement of yearmonth table and it works now.

SELECT '2012/8' AS Period, YearMonth.[Monthno (PS)], assignment_2012.[cluster code] AS cluster, 'Transfer out' AS Remark, -1*Count(*) AS [number], '5' AS sorting
FROM YearMonth INNER JOIN ((assignment_2012 INNER JOIN namesort ON assignment_2012.post = namesort.post) INNER JOIN (SELECT empid
                     FROM   (SELECT empid,
                                    [cluster],
                                    Count(*) AS [empid no]
                             FROM   (SELECT [HKID] as empid,
                                            [Cluster Code] as cluster 
                                     FROM   [assignment_2012]
                                            INNER JOIN yearmonth
                                                    ON [assignment_2012].[year month]
                                                       =
                                                       yearmonth.[Year Month (HM)]
                                     WHERE  yearmonth.[Monthno (PS)] = 243
                                            AND [assignment_2012].hc_adj = 1
                                            AND [assignment_2012].term <> 'Temporary'
                                     UNION
                                     SELECT empid,
                                            [cluster]
                                     FROM   [2012]
                                     WHERE  monthno = 244
                                            AND [2012].term <> 'Temporary'
                                            AND ( div <> 'XXX'
                                                   OR div IS NULL )
                                            AND hc = 1)
                             GROUP  BY [empid],
                                       [cluster]
                             HAVING Count(*) = 1)
                     GROUP  BY empid
                     HAVING Count(*) > 1)  AS Emps ON assignment_2012.ID = Emps.EmpID) ON YearMonth.[Year Month (HM)] = assignment_2012.[Year Month]
WHERE (((assignment_2012.hc_adj)=1) AND ((assignment_2012.term)<>'Temporary'))
GROUP BY YearMonth.[Monthno (PS)], assignment_2012.[cluster code];

Collectives™ on Stack Overflow

Ms Access SQL Query syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related