SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Member ngaisteve1's Avatar
    Join Date
    May 2003
    Location
    Kuala Lumpur, Malaysia (Boleh!)
    Posts
    264
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    inventory system calculation

    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

    Code:
    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 if

  2. #2
    Member ngaisteve1's Avatar
    Join Date
    May 2003
    Location
    Kuala Lumpur, Malaysia (Boleh!)
    Posts
    264
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's ok. I got it fix. :-)

  3. #3
    SitePoint Enthusiast afgmail's Avatar
    Join Date
    Jan 2005
    Location
    Houston, TX
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool!
    Scan completed....
    Brain not found.
    filecabin.com : Notepad2


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •