Accessing Second Record Within Query

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 query to access the second corresponding
record in
the H_Main_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));On 5 Dec 2005 12:12:51 -0800, jon.tjemsland@.gmail.com wrote:

>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 query to access the second corresponding
>record in
>the H_Main_Extra table?
>Thanks in advance for any advice.
Hi Jon,
The code you posted uses the IIf function. This function does not exist
in SQL Server. I suspect that you're using Access. If I'm right, then
you'll probably get better help in an Access group. (This group is for
SQL Server).
Your question is also not completely clear. You say you want to add a
"second corresponding record", but you don't indicate how I would know
which rows are "corresponding". And if there are more than 2, then how
to determine which are first and seceond?
If the IIf's were an error and this question does concern SQL Server,
then please check www.aspfaq.com/5006 to get more information on the
details you need to include in your post to enable us to help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)