ACCESS 数据库 查询语句语法错误

Question

我在sql 里面 写的查询语句  然后丢到 access  里面 报语法错误求答案

Answer 1

SELECT  a.name_gov AS 省名 ,
        e.suma1 AS 省区市 ,
        b.suma1 AS 地市 ,
        c.suma1 AS 县区 ,
        d.suma1 AS 合计
FROM    Work_ReportIncome w
        LEFT JOIN ( SELECT  name_gov ,
                            code_gov
                    FROM    bas_gov
                    WHERE   LEN(code_gov) = 2
                  ) a ON a.code_gov = w.code_province
        LEFT JOIN ( SELECT  code_province ,
                            SUM(a1) AS suma1
                    FROM    Work_ReportIncome
                    WHERE   LEN(code_gov) = 2
                            AND code_year = 2011
                            AND yearType = 2
                            AND isaudit = 1
                    GROUP BY code_province
                  ) e ON e.code_province = a.code_gov
        LEFT JOIN ( SELECT  code_province ,
                            SUM(a1) AS suma1
                    FROM    Work_ReportIncome
                    WHERE   code_gov IN (
                            SELECT  code_gov
                            FROM    bas_gov
                            WHERE   par_code IN ( SELECT    code_gov
                                                  FROM      bas_gov
                                                  WHERE     LEN(code_gov) = 2 ) )
                            AND code_year = 2011
                            AND yearType = 2
                            AND isaudit = 1
                    GROUP BY code_province
                  ) b ON b.code_province = a.code_gov
        LEFT JOIN ( SELECT  code_province ,
                            SUM(a1) AS suma1
                    FROM    Work_ReportIncome
                    WHERE   LEN(code_gov) = 6
                            AND code_year = 2011
                            AND yearType = 2
                            AND isaudit = 1
                    GROUP BY code_province
                  ) c ON c.code_province = A.code_gov
        LEFT JOIN ( SELECT  code_province ,
                            SUM(a1) AS suma1
                    FROM    Work_ReportIncome
                    WHERE   1 = 1
                            AND code_year = 2011
                            AND yearType = 2
                            AND isaudit = 1
                    GROUP BY code_province
                  ) d ON d.code_province = a.code_gov
WHERE   w.code_year = '2011'
        AND w.yearType = '2'
        AND w.isaudit = '1'
GROUP BY a.name_gov ,
        e.suma1 ,
        b.suma1 ,
        C.suma1 ,
        d.suma1
UNION ALLSELECT  '合计' ,
        SUM(a.省区市) ,
        SUM(a.地市) ,
        SUM(a.县区) ,
        SUM(a.合计)
FROM    ( SELECT    a.name_gov AS 省名 ,
                    e.suma1 AS 省区市 ,
                    b.suma1 AS 地市 ,
                    c.suma1 AS 县区 ,
                    d.suma1 AS 合计
          FROM      Work_ReportIncome w
                    LEFT JOIN ( SELECT  name_gov ,
                                        code_gov
                                FROM    bas_gov
                                WHERE   LEN(code_gov) = 2
                              ) a ON a.code_gov = w.code_province
                    LEFT JOIN ( SELECT  code_province ,
                                        SUM(a1) AS suma1
                                FROM    Work_ReportIncome
                                WHERE   LEN(code_gov) = 2
                                        AND code_year = 2011
                                        AND yearType = 2
                                        AND isaudit = 1
                                GROUP BY code_province
                              ) e ON e.code_province = a.code_gov
                    LEFT JOIN ( SELECT  code_province ,
                                        SUM(a1) AS suma1
                                FROM    Work_ReportIncome
                                WHERE   code_gov IN (
                                        SELECT  code_gov
                                        FROM    bas_gov
                                        WHERE   par_code IN (
                                                SELECT  code_gov
                                                FROM    bas_gov
                                                WHERE   LEN(code_gov) = 2 ) )
                                        AND code_year = 2011
                                        AND yearType = 2
                                        AND isaudit = 1
                                GROUP BY code_province
                              ) b ON b.code_province = a.code_gov
                    LEFT JOIN ( SELECT  code_province ,
                                        SUM(a1) AS suma1
                                FROM    Work_ReportIncome
                                WHERE   LEN(code_gov) = 6
                                        AND code_year = 2011
                                        AND yearType = 2
                                        AND isaudit = 1
                                GROUP BY code_province
                              ) c ON c.code_province = A.code_gov
                    LEFT JOIN ( SELECT  code_province ,
                                        SUM(a1) AS suma1
                                FROM    Work_ReportIncome
                                WHERE   1 = 1
                                        AND code_year = 2011
                                        AND yearType = 2
                                        AND isaudit = 1
                                GROUP BY code_province
                              ) d ON d.code_province = a.code_gov
          WHERE     w.code_year = '2011'
                    AND w.yearType = '2'
                    AND w.isaudit = '1'
          GROUP BY  a.name_gov ,
                    e.suma1 ,
                    b.suma1 ,
                    C.suma1 ,
                    d.suma1
        ) AS a

Answer 2

access中要求字段或表用别名的要加上as,你的语句很多都没有,自己检查检查