I have 3 tables: PO, POD, Item
PO: POID, del_status
POD: PODID, POID, item_id, qty_order
Item: item_id, qty_si
When I add first PO, the result is correct. I have 2 rec in POD
item_id | qty_order
1 | 10
2 | 20
result in item table will be
item_id | qty_si
1 | 10
2 | 20
which is correct
When I add the second PO, the result is correct. I have 1 rec in POD
item_id | qty_order
1 | 5
result in item table will be
item_id | qty_si
1 | 15
2 | 20
When I del first PO, the result is not correct.
result in item table will be
item_id | qty_si
1 | 5
2 | 20
item_id 1 is correct but item_id 2 still remain 20!
my way of code is
sql = "SELECT SUM(purchase_order_details.qty_order) as qty_order, purchase_order_details.item_id "
sql = sql & " FROM purchase_order_details, purchase_order WHERE purchase_order.del_status = 'N' "
sql = sql & " AND purchase_order.purchase_order_id = purchase_order_details.purchase_order_id "
sql = sql & " GROUP BY purchase_order_details.item_id"
rs.CursorLocation = 3
rs.Open sql, conn
if not rs.eof then
do while not rs.eof
sItemID = cint(rs("item_id"))
sql = " UPDATE item SET qty_si = " & rs("qty_order") & " WHERE item_id = " & sItemID & " "
conn.Execute( sql )
rs.movenext
loop
end ifyou are only selecting on the sum which is an aggergate function and the item id, of course its not going to have the 3 records, you must make them unique by adding in the POID into the query
SELECT SUM(purchase_order_details.qty_order) as qty_order, purchase_order_details.item_id, purchase_order_details.poid "
sql = sql & " FROM purchase_order_details, purchase_order WHERE purchase_order.del_status = 'N' "
sql = sql & " AND purchase_order.purchase_order_id = purchase_order_details.purchase_order_id "
sql = sql & " GROUP BY purchase_order_details.item_id,purchase_order_details.POID"on the delete issue , you need to delete the entries related to that PO Items i guess. seems you have child records with no parent.
what database are you attempting to use???
if you are using MS-SQL 2000 or MSDE you can write a trigger on delete of PO table that will also delete any child records.Thanks for the reply. I'm using mysql 4.0.24I got it fix.
you will have to waint until 5.0 for triggers......
great!!
PO: POID, del_status
POD: PODID, POID, item_id, qty_order
Item: item_id, qty_si
When I add first PO, the result is correct. I have 2 rec in POD
item_id | qty_order
1 | 10
2 | 20
result in item table will be
item_id | qty_si
1 | 10
2 | 20
which is correct
When I add the second PO, the result is correct. I have 1 rec in POD
item_id | qty_order
1 | 5
result in item table will be
item_id | qty_si
1 | 15
2 | 20
When I del first PO, the result is not correct.
result in item table will be
item_id | qty_si
1 | 5
2 | 20
item_id 1 is correct but item_id 2 still remain 20!
my way of code is
sql = "SELECT SUM(purchase_order_details.qty_order) as qty_order, purchase_order_details.item_id "
sql = sql & " FROM purchase_order_details, purchase_order WHERE purchase_order.del_status = 'N' "
sql = sql & " AND purchase_order.purchase_order_id = purchase_order_details.purchase_order_id "
sql = sql & " GROUP BY purchase_order_details.item_id"
rs.CursorLocation = 3
rs.Open sql, conn
if not rs.eof then
do while not rs.eof
sItemID = cint(rs("item_id"))
sql = " UPDATE item SET qty_si = " & rs("qty_order") & " WHERE item_id = " & sItemID & " "
conn.Execute( sql )
rs.movenext
loop
end ifyou are only selecting on the sum which is an aggergate function and the item id, of course its not going to have the 3 records, you must make them unique by adding in the POID into the query
SELECT SUM(purchase_order_details.qty_order) as qty_order, purchase_order_details.item_id, purchase_order_details.poid "
sql = sql & " FROM purchase_order_details, purchase_order WHERE purchase_order.del_status = 'N' "
sql = sql & " AND purchase_order.purchase_order_id = purchase_order_details.purchase_order_id "
sql = sql & " GROUP BY purchase_order_details.item_id,purchase_order_details.POID"on the delete issue , you need to delete the entries related to that PO Items i guess. seems you have child records with no parent.
what database are you attempting to use???
if you are using MS-SQL 2000 or MSDE you can write a trigger on delete of PO table that will also delete any child records.Thanks for the reply. I'm using mysql 4.0.24I got it fix.
you will have to waint until 5.0 for triggers......great!!