Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2006
    Posts
    15

    Unanswered: How Do I Link 4 Related Tables, Sum All Orders, Subtract All Credits, and List Totals

    Here's the scenario:

    I have 4 tables:

    Item, InvoiceLine, SalesReceiptLine, CreditMemoLine

    InvoiceLine, SalesReceiptLine, and CreditMemoLine are allrelated to the Item table by an ItemID

    What I want to do is to take each item in the Item table and then look in each other table and SUM() the Quantity filed from each individual line item that matches a particular Item.

    The task, ultimatly, is to add up all quantities (for each item) in the InvoiceLine and SalesReceiptLine tbales and then subtract however many exist in the CreditMemoLine table in order to arrive at the total quantity of that particular item sold.

    I tried to join the tables and sum each individual type of quantity:

    SELECT Item.FullName,
    Sum(CreditMemoLine.CreditMemoLineQuantity) AS SumOfCreditMemoLineQuantity,
    Sum(SalesReceiptLine.SalesReceiptLineQuantity) AS SumOfSalesReceiptLineQuantity

    FROM SalesReceiptLine

    INNER JOIN (InvoiceLine INNER JOIN (CreditMemoLine INNER JOIN Item ON CreditMemoLine.CreditMemoLineItemRefListID = Item.ListID) ON InvoiceLine.InvoiceLineItemRefListID = Item.ListID) ON SalesReceiptLine.SalesReceiptLineItemRefListID = Item.ListID

    GROUP BY Item.FullName;

    But that gave me bad results.

    I also tried to do it like this:

    SELECT Item.FullName, SUM(InvoiceLine.Quantity) AS [TOTAL Invoiced], SUM(SalesReceiptLine.SalesReceiptLineQuantity) AS [On Sales Receipts]
    FROM Item, InvoiceLine, SalesReceiptLine
    WHERE ItemListID = InvoiceLineItemRefListID OR Item.ListID = SalesReceiptLine.SalesReceiptLineItemRefListID
    Group BY Item.FullName

    (ys I know I left one table out here - it was just a test)

    But that isn't right either.

    My question is whether or not I can do this in a single query and, if so, if it is efficient or am I better off doing seperate make table queries in ACCESS and then creating a final table from those three to buld my final report off of ...

    Any help would be appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select Item.FullName
         , ( select Sum(CreditMemoLineQuantity) 
               from CreditMemoLine
              where CreditMemoLineItemRefListID 
                  = Item.ListID ) AS SumOfCreditMemoLineQuantity
         , ( select Sum(SalesReceiptLineQuantity) 
               from SalesReceiptLine
              where SalesReceiptLineItemRefListID 
                  = Item.ListID ) AS SumOfSalesReceiptLineQuantity
         , ( select Sum(Quantity) 
               from InvoiceLine
              where InvoiceLineItemRefListID 
                  = Item.ListID ) AS SumOfInvoiceLineQuantity
      from Item
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    15

    Thanks (Again :-)

    Now I feel silly. I should have thought of using multiple SELECT statements ... I was so hung up on trying to join them all!

    I don't suppose you could explain for me how that code will actually execute, or maybe point me to a good book/resource on the subject. I have never yet found a book that explains what SQL is doing. Most of them (ones I have seen so far at least) just give simple examples, but don't go into details.

    I'm also curious as to whether this is the only way to attack the problem. Is there some way to do it with joins, or am I just driving myself crazy trying to figure out how to do the impossible?

    Again, many thanks for your advice!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you can do it with joins -- joins to derived tables
    Code:
    select Item.FullName
         , SumOfCreditMemoLineQuantity
         , SumOfSalesReceiptLineQuantity
         , SumOfInvoiceLineQuantity
      from Item 
    left outer
      join ( select CreditMemoLineItemRefListID
                  , Sum(CreditMemoLineQuantity) 
                 as SumOfCreditMemoLineQuantity
               from CreditMemoLine
             group 
                 by CreditMemoLineItemRefListID
           ) as t1
        on t1.CreditMemoLineItemRefListID 
         = Item.ListID 
    left outer
      join ( select SalesReceiptLineItemRefListID
                  , Sum(SalesReceiptLineQuantity) 
                 as SumOfSalesReceiptLineQuantity
               from SalesReceiptLine
             group 
                 by SalesReceiptLineItemRefListID
           ) as t2
        on t2.SalesReceiptLineItemRefListID 
         = Item.ListID 
    left outer
      join ( select InvoiceLineItemRefListID
                  , Sum(Quantity) 
                 as SumOfInvoiceLineQuantity
               from InvoiceLine
             group 
                 by InvoiceLineItemRefListID
           ) as t3
        on t3.InvoiceLineItemRefListID 
         = Item.ListID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2006
    Posts
    15

    Once again - Many Thanks

    So, do you have a name I can thank you by? Or is it just r937?

    You certainly have been a help. I have tons of SQL books, and have done this in the past but am rusty The basic concepts have always made sense to me, but I'm having a heck of a time applying them in tha accounting world.

    In my previous life I was a LANDesk Software Deployment Engineer and I was dealing with a database full of information on hardware/software inventory. That made a lot more sense to me than accounting :-).

    daniel

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    r937 is my forum handle, username, nickname, whatever

    you may easily ascertain my real name by visiting one of the sites in my signature

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2006
    Posts
    15
    Cryptic much <grin>

    I will visit.

    BTW, how long you been doing SQL/Access/Databases?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you googled r937 I'll bet you could find out more about him...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Rudy's pulled me out of the soup so often it's humilliating
    his sites are an obvious must-visit-regularly

    ...but why '937'

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Feb 2006
    Posts
    15
    Yea - I just visited his site and saw the answer to my question in basically template form. Of course, I'm not shure I would have recognised it right off the bat had he not pointed it out to begin with

    Hopefully someday I'll be that sharp ...

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by daraphaelQODBC
    BTW, how long you been doing SQL/Access/Databases?
    databases since 1979, sql since 1987

    thanks to everyone for the kind words

    izy, it's just my favourite number, that's all

    what, you don't have a favourite number?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2006
    Posts
    15
    no favorite number - but my favorite color is clear ...

Posting Permissions

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