Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2015
    Posts
    3

    Unanswered: subtracting values in multiple records from field value in another table

    Hello,

    I hope there is some one who can help me with a problem I am stuck with.

    I have a database with 3 different tables: contractDB, orderDB and recievedDB. In these tables i have all information about prices, amount, dimension etc.

    In the table ContractDB I write; contract number, prices, total amount ordered (let us say 500 m^3), total amount delivered and delivery week.
    In the table orderDB i have field: contract number as lookup to above table, order number, amount delivered and check-box when marked it means that order is arrived. One contract can have multiple orders, and one order is of maximum 50 m^3.

    My question is how do I go trough all the records in orderDB and lookup in specific contract number, to see if all orders are delivered and then at last subtract amount delivered from total amount ordered.

    I hope this make sens.

    Regards

    Dedaluss

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    use 1 query to subract from the other,
    select * from tOrder, tReceived
    then join tOrder table to the tReceived table (via [orderID] field ?)

    then bring down fields needed esp [Qty] fields,
    the formula for a new field would be: tOrder.Qty - tReceived.Qty as QtyRemain

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a sub seledct/sub qyeryt tol sumamruse the deliveries int ehorders table and then jojn that to the main table.


    frankly its a pants design, but thats a response to another question
    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
  •