使用UNION查询跨表插入数据时遇别名循环引用错误求助

咱们先直接拆解问题根源，再给你修正后的方案：

错误原因分析 #

你遇到的循环引用报错主要来自两个小问题：

• 多余的重复别名：第一个子查询里写了Onshore_Architect As Onshore_Architect这种完全没必要的字段别名——把字段名再命名成它本身，Access会混淆原字段和别名的指向，触发循环引用检测。
• 语法小失误：子查询闭合的]后面多了个多余的点，写成了]. As V，这不符合SQL的语法规范。

修正后的代码 #

可以简化整个查询逻辑，去掉多余的别名，同时修正语法问题：

Q1 = "Insert into Final_PQR (Onshore_Architect,Offshore,PQR_Number,Task,PQR_Type,Status,Latest_Update,Planned_Work,Comments,Remaining_Planned_hours) " & _
     "Select Distinct V.Onshore_Architect,V.Offshore,V.PQR_Number,V.Task,V.PQR_Type,V.Status,V.Latest_Update,V.Planned_Work,V.Comments,V.Remaining_Planned_hours " & _
     "from ( " & _
         "Select distinct Onshore_Architect,Offshore,PQR_Number,Task,PQR_Type,Status,Latest_Update,Planned_Work,Comments,Remaining_Planned_hours from PQR_Deepika_Final " & _
         "UNION " & _
         "Select distinct Onshore_Architect,Offshore,PQR_Number,Task,PQR_Type,Status,Latest_Update,Planned_Work,Comments,Remaining_Planned_hours from PQR_Sonal_Final " & _
     ") As V;"
CurrentDb.Execute Q1

额外优化建议 #

如果两个源表的字段顺序、数据类型完全匹配，还能再简化一层——直接把UNION的结果插入目标表，不用嵌套子查询：

Q1 = "Insert into Final_PQR (Onshore_Architect,Offshore,PQR_Number,Task,PQR_Type,Status,Latest_Update,Planned_Work,Comments,Remaining_Planned_hours) " & _
     "Select distinct Onshore_Architect,Offshore,PQR_Number,Task,PQR_Type,Status,Latest_Update,Planned_Work,Comments,Remaining_Planned_hours from PQR_Deepika_Final " & _
     "UNION " & _
     "Select distinct Onshore_Architect,Offshore,PQR_Number,Task,PQR_Type,Status,Latest_Update,Planned_Work,Comments,Remaining_Planned_hours from PQR_Sonal_Final;"
CurrentDb.Execute Q1

这样代码更简洁，也能避免嵌套查询可能带来的潜在语法问题。

内容的提问来源于stack exchange，提问作者G.Chahar