Beginning SQL assistance

[scottrod]

In an Access97 Db I have the following table:
Table:IntrFac
NameID,AccountID,AbsAmount,Amount,and S2KFormat

Here's sample data:

NameID AccountID AbsAmount Amount S2KFormat
4190 3290 536 536 419.0.2991.00.3290
3330 3290 536 536 333.0.2991.00.3290
3290 2100 536 -536 329.0.2991.00.2100
3290 3330 536 -536 329.0.2991.00.3330
3290 4190 536 -536 329.0.2991.00.4190

In another table, Accounts, I place AccountID and AbsAmount from table Intrfac. I then have the following query:

SELECT DISTINCTROW
IntrFac.NameID, IntrFac.AccountID, IntrFac.AbsAmount, IntrFac.Amount, IntrFac.S2KFormat
FROM IntrFac
INNER JOIN Accounts ON (IntrFac.AbsAmount=Accounts.AbsAmount)
AND (IntrFac.NameID =Accounts.AccountID)
ORDER BY IntrFac.AbsAmount DESC;

The final result I'm after is a recordset that contains entries where NameID=AccountID and the AbsAmount is the same so I have returned, for example 4190,3290,536 and 3290,4190,-536. It's to produce a journal entry to clear intercompany balances. The criteria for clearing is the account ID contains the Name for which the amount is for. If NameID_1,AccountID_1 has a balance, hopefully I can find NameID_2= AccountID_1 and AccountID_2=NameID_1 with the offset balance.
With my query as it's built, I return 3290,2100,-536 because it's true that IntrFac.NameID=Accounts.AccountID and the AbsAmount is equal. I need to exclude this record.
I've tried some Self Join syntax but hunting through Microsoft for example code is becoming tedious. Is a self join possible to return the result I am after?

Thanks in advance,
scott.rodney@tenethealth.com

 

[scottrod]

Believe I might have found the solution with the self join:

SELECT IntrFac.NameID, IntrFac.AccountID, IntrFac_1.AbsAmount, IntrFac_1.Amount, IntrFac.S2KFormat
FROM IntrFac INNER JOIN IntrFac AS IntrFac_1 ON (IntrFac.AbsAmount = IntrFac_1.AbsAmount) AND (IntrFac.AccountID = IntrFac_1.NameID) AND (IntrFac.NameID = IntrFac_1.AccountID);