有一数据库data.mdb.库里有四个表w_1,w_2, w_all
w_1表结构如下:
-----|---------------------------------- |
id | t_id_1 | b_id_1 | tons_1|weight_1 |
1 | 2201 2202 312 985 |
2 | 2208 2209 416 5645 |
3 | 3106 3107 626 145 |
4 | 2201 2202 658 45687 |
5 | 3106 2209 785 4875 |
_____|___________________________________|w_2表结构如下:
-----|---------------------------------- |
id | t_id_2| b_id_2|tons_2|weight_2
1 | 6881 6882 368 389 |
2 | 7308 7309 616 7585 |
3 | 9236 9237 889 965 |
4 | 6881 6882 865 8847 |
5 | 9236 7309 785 4875 |
_____|___________________________________|w_all表结构如下:
-----|------------------------------- |
id | w_id | tons_all|weight_all |
1 | 2201 A B |
2 | 2202 |
3 | 2208 |
4 | 2209 C D |
5 | 3106 |
6 | 3107 |
7 | 6881 |
8 | 6882 |
9 | 7308 |
10 | 7309 |
11 | 9236 |
12 | 9237 |
_ ___|________________________________|
1,先说明了一下w_all表的结构,表中wr_id 中wr_id的内容包括表wr_1中t_id_1和b_id_1和表wr_2中t_id_2和b_id_2中的内容,即表w_1和w_2所有的t_id_1和b_id_1,t_id_2,b_id_2内容都能在表w_all中w_id字段下找到.
2,表w_1和表w_2的内容是不断增加的.但t_id_*和b_id_*号总是包括于表w_all中w_id记录中的.现在想实现的功能是:当表w_1,或表w_2有内容增加时,在表w_all中找到相应的w_id号,并把tons_1或tons_2的内容加到表w_all中的tons_all字段中.把weight_1或weight_2的内容加到表w_all中的weight_all字段中.例如:
在表w_1中,增加了一条记录为:t_id_1=2201 t_id_1=2209 tons_1=560 weight_1=682
则表w_all中,w_id为2201的记录相对应的tons_all和weight_all应执行下面的动作:tons_all=A+560 weight_all=B+682
2209的记录相对应的tons_all和weight_all应执行下面的动作:tons_all=C+560 weight_all=D+682
用ADO+access要怎样写代码,请教大家
w_1表结构如下:
-----|---------------------------------- |
id | t_id_1 | b_id_1 | tons_1|weight_1 |
1 | 2201 2202 312 985 |
2 | 2208 2209 416 5645 |
3 | 3106 3107 626 145 |
4 | 2201 2202 658 45687 |
5 | 3106 2209 785 4875 |
_____|___________________________________|w_2表结构如下:
-----|---------------------------------- |
id | t_id_2| b_id_2|tons_2|weight_2
1 | 6881 6882 368 389 |
2 | 7308 7309 616 7585 |
3 | 9236 9237 889 965 |
4 | 6881 6882 865 8847 |
5 | 9236 7309 785 4875 |
_____|___________________________________|w_all表结构如下:
-----|------------------------------- |
id | w_id | tons_all|weight_all |
1 | 2201 A B |
2 | 2202 |
3 | 2208 |
4 | 2209 C D |
5 | 3106 |
6 | 3107 |
7 | 6881 |
8 | 6882 |
9 | 7308 |
10 | 7309 |
11 | 9236 |
12 | 9237 |
_ ___|________________________________|
1,先说明了一下w_all表的结构,表中wr_id 中wr_id的内容包括表wr_1中t_id_1和b_id_1和表wr_2中t_id_2和b_id_2中的内容,即表w_1和w_2所有的t_id_1和b_id_1,t_id_2,b_id_2内容都能在表w_all中w_id字段下找到.
2,表w_1和表w_2的内容是不断增加的.但t_id_*和b_id_*号总是包括于表w_all中w_id记录中的.现在想实现的功能是:当表w_1,或表w_2有内容增加时,在表w_all中找到相应的w_id号,并把tons_1或tons_2的内容加到表w_all中的tons_all字段中.把weight_1或weight_2的内容加到表w_all中的weight_all字段中.例如:
在表w_1中,增加了一条记录为:t_id_1=2201 t_id_1=2209 tons_1=560 weight_1=682
则表w_all中,w_id为2201的记录相对应的tons_all和weight_all应执行下面的动作:tons_all=A+560 weight_all=B+682
2209的记录相对应的tons_all和weight_all应执行下面的动作:tons_all=C+560 weight_all=D+682
用ADO+access要怎样写代码,请教大家
conn.begintrans
strsql="Insert into w_l(t_id_1,b_id_1,tons_1,weight_1)values("& t_id &","& b_id &","& tons &","& weight &")"
conn.execute strsql
strsql="Update w_all Set tons_all=tons_all+"& tons &",weight_all=weight_all+"& weight &" Where w_id="& t_id
conn.execute
conn.committrans
对w_2的操作类似,这里就不写了
谢谢大家的关注,谢谢
strsql="Insert into w_l(t_id_1,b_id_1,tons_1,weight_1)values("& t_id &","& b_id &","& tons &","& weight &")"
上面此句语法不对,
不知为什么?
继续请教各位高手谢谢了。
试试
strsql=" Insert into w_l (t_id_1,b_id_1,tons_1,weight_1) values ("& t_id &","& b_id &","& tons &","& weight &")"
比如t_id是字符型,要写成values('"& t_id &"',"& b_id.....以此类推
conn.BeginTrans
Do While Not rst.EOF
If rst.Fields("tons_1") <>0 Then
strid = rst.Fields("id")
mysql = "insert into w_1 SELECT t_id_1,b_id_1,tons_1,weight_1 FROM roll where id= " & strid
conn.Execute mysql
'当w_1有新的记录加入时,把在相对应ID的记录插入到表w_all,相同的ID记录中,并累加求tons_all 和weight_all的总和
strsql = "insert into wr_l(t_id_1,tons_1,weight_1)values(" & t_id_1 & "," & tons_1 & "," & weight_1 & ")"
conn.Execute strsql
strsql = "update w_all Set tons_all=tons_all+" & tons_1 & ",weight_all=weight_all+" & weight_1 & " Where w_id=" & t_id_1
conn.Execute strsql
conn.CommitTrans
End If
总是提示:strsql = "insert into wr_l(t_id_1,tons_1,weight_1)values(" & t_id_1 & "," & tons_1 & "," & weight_1 & ")" 此句语法错误,不知为什么请教大家了,谢谢
conn.Execute strsql
conn.CommitTrans
但提示错误,不能通过,请大家帮我看看谢谢.