各位前辈/老师: 请教一个简单的问题,在Access数据库中,我想将View1的查询结果和View2的查询结果记录组合在一起,没有任何条件,不存在记录重复问题和主键问题
表:
tblA 字段 A,B,C,D
tblB 字段 B,C,D,E查询: View1 字段: B,C,D
View2 字段:B,C,D操作:
假设View1有10条记录,View2有20条记录,则结果应该是30条记录
Create View ViewTbl B,C,D As Select * From View1 Union View2 '提示子查询不能联合 敬请指点一下,谢谢
表:
tblA 字段 A,B,C,D
tblB 字段 B,C,D,E查询: View1 字段: B,C,D
View2 字段:B,C,D操作:
假设View1有10条记录,View2有20条记录,则结果应该是30条记录
Create View ViewTbl B,C,D As Select * From View1 Union View2 '提示子查询不能联合 敬请指点一下,谢谢
select * from view1
union
select * from view2
PrdPlnCam,PrdPlnMpn两个视图的别名,烦请再次指点
下面是我创建查询的程序
arr1 = Array("Camera", "MPn", "Game")
For i = 0 To UBound(arr1)
Call CreatDBView("View_Prd_Pln_" & arr1(i), "", "Select * From Tbl_Prd_Pln_" & arr1(i))
Call CreatDBView("View_Prd_PO_" & arr1(i), "", "Select 表单编码,工单编号,订单编号,产品型号,订单数量,出货日期 From Tbl_Prd_Pln_" & arr1(i))
Next i
上面创建视图能够得到正确的结果
' '所有订单总视图...................(这句有问题)
' Call CreatDBView("View_Prd_PO", "", "Select * From View_Prd_Pln_Camera Union Select * From View_Prd_Pln_MPn")'创建视图通用过程
Sub CreatDBView(ViewName As String, ColAlias As String, sql As String)
lblIndicator.Caption = dbTableNameC & "资料视图初始化!"
With Conn
Debug.Print sql
Debug.Print "Create View " & ViewName & " " & ColAlias & " AS " & sql
.Execute "Create View " & ViewName & " " & ColAlias & " AS " & sql
End With
lblIndicator.Caption = dbTableNameC & "资料视图初始化完成!"
End Sub
烦请再次指点,谢谢
改用如下方式试试create view View3 as
select * from(
select * from view1
union
select * from view2)
Tiger_Zhao前辈: 非常感谢你的指点,根据你的指点运行成功,语句如下
Select * From( Select * From (Select * From View_Prd_Pln_Camera Union Select * From View_Prd_Pln_MPn) Union Select * From View_Prd_Pln_Game)
select * from (
select * from View_Prd_Pln_Camera
union
select * from View_Prd_Pln_MPn
union
select * from View_Prd_Pln_Game)
select * from View_Prd_Pln_Camera
union
select * from View_Prd_Pln_MPn
union
select * from View_Prd_Pln_Game)
这种方式经测试也能正常运行,语句简单一点
顺便再问一点关于变量的问题 arr1 = Array("SMT", "COB", "HND", "ASS", "PAK")
For i = 0 To UBound(arr1)
str1 = "PrdRpt_" & arr1(i) & "."
Call CreatDBView("View_Prd_Rpt_" & arr1(i), "(表单编码_行号,产品系列,工序名称)", _
"Select PrdRpt.表单编码 & '_'& PrdRpt_SMT.行号,PrdRpt.产品系列,PrdRpt.工序名称 " & _
"From Tbl_Prd_Rpt As PrdRpt ,Tbl_Prd_Rpt_" & arr1(i) & " As PrdRpt_" & arr1(i) & _
" Where PrdRpt.表单编码=" & str1 & "表单编码")
Next i上述语句顺利通过,但是我要PrdRpt.表单编码 & '_'& PrdRpt_SMT.行号这里的SMT替换成变量的形式,怎么都替换不成功PrdRpt.表单编码 & '_'& "PrdRpt_" & arr1(i) & "行号..... '提示出现语法错误
'你的SQL支持&操作符吗,"& '_ ' &"部分什么意思?
arr1 = Array( "SMT ", "COB ", "HND ", "ASS ", "PAK ")
arr2 = Array("PrdRpt.表单编码 & '_'& PrdRpt_SMT.行号", "PrdRpt.表单编码 & '_'& PrdRpt_COB.行号", "PrdRpt.表单编码 & '_'& PrdRpt_HND.行号", "PrdRpt.表单编码 & '_'& PrdRpt_ASS.行号", "PrdRpt.表单编码 & '_'& PrdRpt_PAK.行号")
Select PrdRpt.表单编码 & '_ ' & PrdRpt_ SMT.行号,......
原语句"Select PrdRpt.表单编码 & '_ ' & PrdRpt_ " & arr1(i) & ".行号,PrdRpt.产品系列,PrdRpt.工序名称,PrdRpt.生产日期," & str1 & "工单编号,vPrdP0.产品型号," & _
str1 & "计量单位," & "vPrdP0.订单数量,vPrdP0.出货日期," & str1 & "生产数量," & str1 & "线别名称," & _
str1 & "生产项目," & str1 & "备注," & str1 & "应转入数, " & str1 & "应转出数," & str1 & "审核," & str1 & "锁定," & _
str1 & "打开,PrdRpt.录入员,PrdRpt.录入时间 " & _
"From Tbl_Prd_Rpt As PrdRpt ,Tbl_Prd_Rpt_" & arr1(i) & " As PrdRpt_" & arr1(i) & " Inner Join View_Prd_PO As vPrdP0" & _
" ON " & str1 & "工单编号 = vPrdP0.工单编号 Where PrdRpt.表单编码=" & str1 & "表单编码")调试结果(立即窗口):
Select PrdRpt.表单编码 & '_ ' & PrdRpt_ SMT.行号,PrdRpt.产品系列,PrdRpt.工序名称,PrdRpt.生产日期,PrdRpt_SMT.工单编号,vPrdP0.产品型号,PrdRpt_SMT.计量单位,vPrdP0.订单数量,vPrdP0.出货日期,PrdRpt_SMT.生产数量,PrdRpt_SMT.线别名称,PrdRpt_SMT.生产项目,PrdRpt_SMT.备注,PrdRpt_SMT.应转入数, PrdRpt_SMT.应转出数,PrdRpt_SMT.审核,PrdRpt_SMT.锁定,PrdRpt_SMT.打开,PrdRpt.录入员,PrdRpt.录入时间 From Tbl_Prd_Rpt As PrdRpt ,Tbl_Prd_Rpt_SMT As PrdRpt_SMT Inner Join View_Prd_PO As vPrdP0 ON PrdRpt_SMT.工单编号 = vPrdP0.工单编号 Where PrdRpt.表单编码=PrdRpt_SMT.表单编码
Select PrdRpt.表单编码 + '_' + PrdRpt_SMT.行号,...