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

    Question Unanswered: Is this possible?

    I have before me one (1) table of shipping and receiving logs to and from a location. Each location has one (1) table of shipping logs. Format is similar to below.
    - DLVR describes if the part was received (in) or shipped (out) from the location. 'SHIP qty' cant exceed 'RECV qty' (cant ship something you don't have)
    - description is the part name
    - user is the name of the delivery person
    - partNo is the part number
    - qty is the number of parts being received (in) or shipped (out)

    Example:
    DLVR: Desc: User: PartNo: Qty:
    RECV Widget John 1234 2
    SHIP Widget Jane 1234 1
    SHIP Widget Steve 1234 1
    RECV Thingamajig Adam 5678 4
    SHIP Thingamajig Eve 5678 2
    SHIP Thingamajig Adam 5678 1
    RECV Doohickey Steve 9012 3

    From the above, it is possible to determine what parts are still at the location (Part RECV qty minus Part SHIP qty). I could write a program to compare record by record and calculate it out, but this is slow and error prone. Is there a way to get SQL to do this for me? I tried to google it but dont even know what to look for. It would ideally return something like the following:

    Example:
    Desc: PartNo: Qty:
    Thingamajig 5678 1
    Doohickey 9012 3

    Thanks!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Does this give you any ideas?

    SELECT PartNo, Sum(IIf(DLVR = "RECV", Qty, 0)) AS Received, Sum(IIf(DLVR = "SHIP", Qty, 0)) AS Shipped
    FROM TableName
    GROUP BY PartNo

    If you want a single value this should work:

    Sum(IIf(DLVR = "RECV", Qty, -Qty))
    Paul

  3. #3
    Join Date
    Feb 2012
    Posts
    2

    Works Beautifully!

    The single value is exactly what I was looking for!
    It saved me an hour or so of tedious coding and bombarding the database with thousands of queries.

    You're a Godsend.
    Thanks again!

    This thread is now resolved, but I don't know how to close it.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help! Welcome to the site by the way. I don't think there's a way to mark a thread solved on this forum.
    Paul

Posting Permissions

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