Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Posts
    43

    Unanswered: Simple Union Query that adds payments

    Hi,
    I'm now to using Union query’s and would like to sum my payments in the query below.

    SELECT [PayrollNo],[Name], [payment]
    FROM [Table1]


    UNION SELECT [PayrollNo],[Name], [payment]
    FROM [Table2]

    Thank you for your help

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    a) If there is a relationship between Table1.PayrollNo and Table2.PayrollNo, you can perform the operation in one query by using an INNER JOIN (or another kind of join -LEFT or RIGHT-, depending on the nature of the relationship between both tables):
    Code:
    SELECT Sum(Table1.payment) + Sum(Table2.payment) AS Total
    FROM ( SELECT Table1.payment, Table2.payment
             FROM Table1 INNER JOIN 
                  Table2 ON Table1.PayrollNo = Table1.PayrollNo
         );
    b) Otherwise, you'll need to use two queries (at least in Access):
    1. Union query (name = Query_Union):
    Code:
    SELECT *
    FROM Table1 
    UNION ALL
    SELECT *
    FROM Table2
    2. Summation query:
    Code:
    SELECT Sum(payment) AS Total
    FROM Query_Union;
    Have a nice day!

Posting Permissions

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