Sum function in nested select

[LeoNdec]

I've written the following query to find the charge by procedure for the current month, previous month, and fiscal year. The query works find until I try to join a new table (Rendering AS r ON r.[Rendering ID] = c.[Rendering Id]) so that I can pull in the name of the Rendering Provider. When I join the table either in another nested select or through an INNER JOIN, my sum results are quadrupled! What am I doing wrong? I've tried everything I know how to do except for a UNION, which won't work because in want a new column and not new rows of data?? Any and all advice will be greatly appreciated. (Sorry for the messy code - pulled query right out of Access).

====

SELECT cc.[Item Name], (SELECT IIF(ISNULL(Sum(c2.[Billed Charge])), 0, CCur(Sum(c2.[Billed Charge]))) FROM Charges AS c2 WHERE c2.[CPT Code] = c.[CPT Code] AND Month(c2.[Claim Date]) = (Month(Tempvars!ReportDate) - 1) AND c2.[Claim Date] > Tempvars!FiscalYearStart-1) AS [Curr Mth Ins Charge], (SELECT IIF(ISNULL(Sum(c3.[Billed Charge])), 0, CCur(Sum(c3.[Billed Charge]))) FROM Charges AS c3 WHERE c3.[CPT Code] = c.[CPT Code] AND Month(c3.[Claim Date]) = (Month(Tempvars!ReportDate) - 2) AND c3.[Claim Date] > Tempvars!FiscalYearStart-1) AS [Prev Mth Ins Charge], IIf(IsNull(Sum(c.[Billed Charge])),0,CCur(Sum(c.[Billed Charge]))) AS [FY Ins Charge] INTO [CPT Summary]
FROM Charges AS c INNER JOIN CPT_Codes AS cc ON c.[CPT Code] = cc.[CPT Code]
WHERE (((1)=1) AND ((c.[Claim Date]) Between [Tempvars]![FiscalYearStart] And [Tempvars]![FiscalYearEnd]))
GROUP BY cc.[Item Name], c.[CPT Code];

 

[Galaxiom]

Joining another table in an aggregate query will produce a record for each of the unique Group By combinations of records in both tables.

You need to join your whole aggregate query back to the table with the names rather than joining before the aggregate is applied.

 

[LeoNdec]

Galaxiom - Thanks for your reply. My next question is, "How?" Honestly, I'm not much of a programmer and have picked up every bit of skill I have from the "Step by Step Access 2010" book and the Internet over the last few weeks. I think youre telling me to put my select statement in the FROM clause, but I have no idea how to do this. I'll search the web, but if you could reply with a simple syntax example, that would be awesome.

Thanks again!

 

[Galaxiom]

Easiest way is to use the query designer to make another query based on the aggregate query. Drag the query into it just like you would with a table.

 

[LeoNdec]

Thanks, Galaxiom! Your "quick fix" did the trick. Again, I really appreciate your help.