VB中SQL语言对一个表进行添加数据使用另一个表的内容
表1(mylotdata):TGT_LOT_NBR13451567表2(myrejectdata):LOT_NBRREJECT_CATGREJECT_QTY1345PA1...
表1(mylotdata):
TGT_LOT_NBR
1345
1567
表2(myrejectdata):
LOT_NBR REJECT_CATG REJECT_QTY
1345 PA 131
1345 OP 141
1567 PA 20
1567 OP 10
使用SQL语言把表2内容添加到表1,如下:
TGT_LOT_NBR PA OP
1345 131 141
1567 20 10
下面是我做的一些动作,都有问题:
Private Sub Command3_Click()
Dim db As DAO.Database
Set db = OpenDatabase(App.Path & "\123.mdb", False, False, ";pwd=kiss")
db.Execute "ALTER TABLE mylotdata ADD COLUMN PA int" '我先试PA这种情况先在mylotdata表中加入例PA
db.Execute "update mylotdata set PA = myrejectdata.REJECT_QTY from myrejectdata where myrejectdata.LOT_NBR = mylotdata.TGT_LOT_NBR and myrejectdata.REJECT_CATG ='PA'"
'上面这种试过不能通过,始终报警下面为我又用的一种方式
db.Execute "insert into mylotdata (PA) select REJECT_QTY from myrejectdata where mylotdata.TGT_LOT_NBR = myrejectdata.LOT_NBR and myrejectdata.REJECT_CATG ='PA'"
'以上这种方式也不能实现,由于数据库是上万条记录,所以只能求助SQL语言解决.
end sub
希望大家帮助解决一下,万分感谢!! 展开
TGT_LOT_NBR
1345
1567
表2(myrejectdata):
LOT_NBR REJECT_CATG REJECT_QTY
1345 PA 131
1345 OP 141
1567 PA 20
1567 OP 10
使用SQL语言把表2内容添加到表1,如下:
TGT_LOT_NBR PA OP
1345 131 141
1567 20 10
下面是我做的一些动作,都有问题:
Private Sub Command3_Click()
Dim db As DAO.Database
Set db = OpenDatabase(App.Path & "\123.mdb", False, False, ";pwd=kiss")
db.Execute "ALTER TABLE mylotdata ADD COLUMN PA int" '我先试PA这种情况先在mylotdata表中加入例PA
db.Execute "update mylotdata set PA = myrejectdata.REJECT_QTY from myrejectdata where myrejectdata.LOT_NBR = mylotdata.TGT_LOT_NBR and myrejectdata.REJECT_CATG ='PA'"
'上面这种试过不能通过,始终报警下面为我又用的一种方式
db.Execute "insert into mylotdata (PA) select REJECT_QTY from myrejectdata where mylotdata.TGT_LOT_NBR = myrejectdata.LOT_NBR and myrejectdata.REJECT_CATG ='PA'"
'以上这种方式也不能实现,由于数据库是上万条记录,所以只能求助SQL语言解决.
end sub
希望大家帮助解决一下,万分感谢!! 展开
1个回答
展开全部
Private Sub Command3_Click()
Dim db As DAO.Database
Set db = OpenDatabase(App.Path & "\123.mdb", False, False, ";pwd=kiss")
db.Execute "ALTER TABLE mylotdata ADD PA int,OP int"
db.Execute "update mylotdata inner join myrejectdata on mylotdata.TGT_LOT_NBR=myrejectdata.LOT_NBR set mylotdata.PA = myrejectdata.REJECT_QTY where myrejectdata.REJECT_CATG ='PA'"
db.Execute "update mylotdata inner join myrejectdata on mylotdata.TGT_LOT_NBR=myrejectdata.LOT_NBR set mylotdata.OP = myrejectdata.REJECT_QTY where myrejectdata.REJECT_CATG ='OP'"
db.close
end sub
Dim db As DAO.Database
Set db = OpenDatabase(App.Path & "\123.mdb", False, False, ";pwd=kiss")
db.Execute "ALTER TABLE mylotdata ADD PA int,OP int"
db.Execute "update mylotdata inner join myrejectdata on mylotdata.TGT_LOT_NBR=myrejectdata.LOT_NBR set mylotdata.PA = myrejectdata.REJECT_QTY where myrejectdata.REJECT_CATG ='PA'"
db.Execute "update mylotdata inner join myrejectdata on mylotdata.TGT_LOT_NBR=myrejectdata.LOT_NBR set mylotdata.OP = myrejectdata.REJECT_QTY where myrejectdata.REJECT_CATG ='OP'"
db.close
end sub
本回答被提问者采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询