# Thread: Summing in a union query (2003)

1. ## 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. ## 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. ## 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
•