Match Table Fields

[vinsavant]

Before
The following table shows how the Credit transaction (ID 1) has a corresponding offsetting entry in (ID 3).

ID	Cost Center	Line of Business	Debit	Credit	Event ID
1	10056	Retail	$5,000	$0.00
2	10799	Retail	$1,450	$0.00
3	10056	Retail	$0.00	$5,000
4	10022	Commercial	$3,000	$0.00

Every transaction comes into the table as either a Debit or a Credit. Each transaction has a unique ID and it is assigned to a Cost Center and a Line of Business.


Our goal is to produce a query that will extract transactions that maybe related. In other words, to find a debit amount with an offsetting credit amount in order to assign them an Event ID number in another column. In order to make a match, both transactions (the Credit and the Debit) must have the same Cost Center, belong to the same Line of Business and both the Debit and the Credit amount must be the same value.

After
The query results only shows transactions that are related.

ID	Cost Center	Line of Business	Debit	Credit	Event ID
1	10056	Retail	$5,000	$0.00
3	10056	Retail	$0.00	$5,000

[June7]

You could try a self-join of the table. The query will not be editable.

SELECT [Transactions].CostCenter, [Transactions].LineOfBusiness, [Transactions].ID, [Transactions].Debit, Table1_1.ID, Table1_1.Credit
FROM Transactions AS Table1_1 INNER JOIN Transactions ON (Table1_1.Credit=[Transactions].Debit) AND (Table1_1.LineOfBusiness=[Transactions].LineOfBusiness) AND (Table1_1.CostCenter=[Transactions].CostCenter);