Using Subquery "summed totals" in a Primary Query. Not working as desired. Please help.

[lawrecp]

I have a data set of line item payments that typically have 2-3 line items (partial payments) for one ClaimID. I am trying to create a query with a clean output of expected revenue per ClaimID. If the partial payments were added together then subtracted from "aquisition cost" then it would show a positive revenue. I created a subquery to consolidate the ClaimIDs and SUM the total payments but when I add this subquery to my Primary Query and drag the [SumTotalPayments] into the query, it doesnt consolidate the ClaimIDs, only adds the [SumTotalPayments] to each line item. Hoping there is an easy fix. Spent too many hours trying to figure it out. Any help would be greatly appreciated. Happy to make clarifications if my explanation doesn't make sense. Pictures with #1 are my Primary Query and #2 is by subquery and #3 is combined query. Thankyou, Phillip

[NoellaG]

First of all, the conditions CPT= "j9022" and servicedate between should probably be in the WHERE clause and not the having. This way the conditions are applied before the grouping and the query would be faster.
If you need the revenue per claim, I would say you need to group by claim, with the sum of the payments, and the sum of the costs. You can use this query as a basis to calculate sum of payments - sum of costs

[June7]

Might consider building a report and use its Sorting & Grouping with aggregate calculations. This will allow showing detail records as well as summary calcs.

Otherwise, query needs to aggregate both TotalActualA and Payments, as already noted by NoellaG.

Producing your query 3 without the payments sum repeating can be done but gets really complicated and would likely slow query performance.