Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing in a union query (2003)

    Hi ,
    I dont have much experience with union queries but what I am trying to do is take the results of 2 queries and combine the reults for the providers and come up with a summed qty,Calc Cvrd Amt, and Calc Cvrd Chg Amt. How can I combine these? The code is the 2 queries that are combined with a union seperating them.



    SELECT [TN 4138 PHCS].[Internal number], [TN 4138 PHCS].[Calc ID], [TN 4138 PHCS].[Last name], [TN 4138 PHCS].[First Name], [TN 4138 PHCS].[Middle Initial], Sum([4138_denver].[Calc Srvc Qty]) AS QTY, Sum([4138_denver].[Calc Cvrd Chg Amt]) AS Charges, Sum([4138_denver].[Calc Cvrd Amt]) AS Allowed
    FROM [TN 4138 PHCS] LEFT JOIN 4138_denver ON [TN 4138 PHCS].[Internal number] = [4138_denver].[Srvc Prv Id]
    GROUP BY [TN 4138 PHCS].[Internal number], [TN 4138 PHCS].[Calc ID], [TN 4138 PHCS].[Last name], [TN 4138 PHCS].[First Name], [TN 4138 PHCS].[Middle Initial]
    ORDER BY [TN 4138 PHCS].[Last name];
    UNION SELECT [TN 4138 PHCS].[Internal number], [TN 4138 PHCS].[Calc ID], [TN 4138 PHCS].[Last name], [TN 4138 PHCS].[First Name], [TN 4138 PHCS].[Middle Initial], Sum([4138_STL].[Calc Srvc Qty]) AS QTY, Sum([4138_STL].[Covered Charge]) AS Charges, Sum([4138_STL].[Drvd Cvrd Amt]) AS Allowed
    FROM [TN 4138 PHCS] LEFT JOIN 4138_STL ON [TN 4138 PHCS].[Calc ID] = [4138_STL].[Srvc D1 Prv Nbr]
    GROUP BY [TN 4138 PHCS].[Internal number], [TN 4138 PHCS].[Calc ID], [TN 4138 PHCS].[Last name], [TN 4138 PHCS].[First Name], [TN 4138 PHCS].[Middle Initial]
    ORDER BY [TN 4138 PHCS].[Last name];

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summing in a union query (2003)

    You should remove the semi-colon after the first query.

    Instead of combining two totals queries, you should do this in two steps:

    1) Create a union query that combines all records (i.e. no grouping and summing)
    2) Create a totals query based on the union query in which you specify the grouping and summing.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing in a union query (2003)

    that works. thanks

Posting Permissions

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