Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    45

    Unanswered: Union query blues....

    I have two tables where invoicing information is put in - one tracks parts that are ordered, the other tracks workscope (labor) and both of the tables are linked individually to another table (BMR table.)

    I want to create a query that will sum up all the invoices in the parts table, all invoices from the workscope table and add those two sums up to get an actual cost total.

    I created a union query and it works fine if there is information in only 1 of the tables - if there is information in the parts table AND workscope table, then it returns two records for the BMR instead of summing the two totals and giving me one record. Here is my SQL statement:

    SELECT DISTINCTROW tblParts.[BMR#], Sum(tblParts.InvAmount) AS [Sum Of InvAmount]
    FROM tblParts
    GROUP BY tblParts.[BMR#]
    UNION SELECT DISTINCTROW tblWorkScope.[BMR#], Sum(tblWorkScope.InvAmount) AS [Sum Of InvAmount]
    FROM tblWorkScope
    GROUP BY tblWorkScope.[BMR#];

    What am I doing wrong?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT [BMR#]
     , Sum([Sum Of InvAmount]) as One_Sum_Overall
      FROM (
         SELECT tblParts.[BMR#]
          , Sum(tblParts.InvAmount) AS [Sum Of InvAmount]
           FROM tblParts
       GROUP BY tblParts.[BMR#]
     UNION ALL
         SELECT tblWorkScope.[BMR#]
          , Sum(tblWorkScope.InvAmount) 
           FROM tblWorkScope
       GROUP BY tblWorkScope.[BMR#]
           ) as data
    GROUP
        BY [BMR#]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    45

    Talking Thank you!

    Thank you, thank you, thank you!

    That worked beautifully!

Posting Permissions

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