Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    7

    Unanswered: how to perform subtraction in access2007

    hyyyyy..

    i have one form to issue spares to users..in this form o have one field "quantity to be issued" let suppose i have 50 in total n i am issuing 5 out of them to user..now i want that spare shud be 45 in main table..automatically...dnt knw where to put code for it..

    thanx

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggest that if you are "issuing" items then to me that suggests they may get retuyrned.
    instead I'd use a transaction table underneath the products table
    the transaction table recors every stock movement (ie deliveries form suppliers, returns etc add to stock, sales, issues, write off subtract from stock). actual stock itself is calculated when needed and is the difference between items that add to stock, and items tha subtract from stock
    you can build in transactions codes for all manner of stock movements. eg you could 'reserve' iotemns so they are no longer perceived as free stock.

    you can cater for stock counts and run you free stock calculation from that stock count
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    7
    thanx 4 considering my question...bt i still hav problemss..how to do it...i just have one main table containg the specs of all record(part no,main eqpt,nomen,location ,total quantity)...i just want to make a form to issue any spare..it contains info like
    part no
    main eqpt
    nomenclature
    location
    quantity to be issued

    i just want whenevr i fill this form ..these details are also copied to another new table lets c t2..when i press okk..

    n second thing...when 5 items are issued..i want, it is subtracted from main table(total quantity) and when i search that item ..the subtracted(updated)
    quantity is shown....
    sililarly if there is only 1 item of any spare ..n i issued that one item..so it shud b deleted from main table

    i hope i have explained my problems

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no y'don't get it
    don't store what is called derived information, ie stuff that can be dreerived from data elsewhere in the DB.

    almost certainly there should be no such thing as 'total quantity'
    you can always derive a stotck quantity as the sum of the stock movements./transactions
    In database terms you need to separate a product from a specific instance of the product.
    a product could be defines as its description, colour, dimensions and so on. having defiened a product then somewhere else you would have how many of that product exist, usually modelled as specifric movements/transactions. eg 100 delivered, 22 sold,23 sold,15 returned, 5 scrapped, 20 committed
    meaning
    100 bought in
    stock on hand is 75 (100 - 22 - 23 +15 -5)
    Free stock is stock on hand less forward commitments, ie 55
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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