multiple table difference

[jon.tjemsland]

Hello,

I'm attempting to use a query to add up the charges in a record from a
table and two corresponding records in another table and then return a
result if there is a difference between the known total and these
calculations. My query below works fine for adding up charges from a
record in the H_MAIN table and one record from the H_Main_Extra table.
What would I add into this to access the second corresponding record in
the H_Main_Extra extra table?

Thanks in advance for any advice.

Jon


SELECT [TotalSub].Tif, [TotalSub].c1, [TotalSub].c2, [TotalSub].c3,
[TotalSub].c4, [TotalSub].CalcTotal, [TotalSub].ExtractedTotal,
[TotalSub].AmtDiff
FROM (SELECT H_MAIN.Tif, H_MAIN.Changes,
IIf(IsNull(H_MAIN.[24f_Charges1]),"0",H_MAIN.[24f_Charges1]) AS c1,
IIf(IsNull(H_MAIN.[24f_Charges2]),"0",H_MAIN.[24f_Charges2]) AS c2,
IIf(IsNull(H_MAIN_Extra.[24f_Charges1]),"0",H_MAIN_Extra.[24f_Charges1])
AS c3,
IIf(IsNull(H_MAIN_Extra.[24f_Charges2]),"0",H_MAIN_Extra.[24f_Charges2])
AS c4, ,Val([c1])+Val([c2])+Val([c3])+Val([c4]) AS CalcTotal,
IIf(IsNull(H_MAIN.[28_Total_Charges]),"0",Val(H_MAIN.[28_Total_Charges]))
AS ExtractedTotal, Abs([CalcTotal]-[ExtractedTotal]) AS AmtDiff
FROM H_MAIN LEFT JOIN H_Main_Extra ON H_MAIN.Id =
H_Main_Extra.ParentId) AS TotalSub WHERE ((([TotalSub].[AmtDiff])>0));

 

[Tom Ellison]

Dear Jon:

For simplicity, could you just write a query that adds up all the rows from
the Extra table for each group of rows, then join that with your MAIN query?

Tom Ellison

 

[jon.tjemsland]

Tom,

Could you show me an example of this?


Thanks

Jon

 

[Tom Ellison]

Dear Jon:

For my convenience, please respond to me with the complete text of previous
messages, or at least all relevant portions. I answer perhaps a hundred
questions here a week, and I can't keep everyone's details straight. It
really helps, thanks!

The intermediate query would be like this:

SELECT ParentId, SUM(AmtDiff)
FROM H_Main_Extra
GROUP BY ParentId

Best I can tell, it is the sum of all the AmtDiff you want. Is that right.
Join the above to H_MAIN on ParentId = Id. I'm thinking that's what you're
after here.

Tom Ellison