Match off one duplicate

[RMS123]

Hi there,

I was wondering if anyone had a better solution for dealing with duplicates.

I would like to Match off One duplicate and show the other in my results.

Table 1:

Book1
	E	F	G
1	TRACE	ACCOUNT	AMOUNT
2	1	111	100
3	2	222	200
4	3	333	300
5	4	444	400
6	5	555	500
Sheet2

Table 2:

Book1
	A	B	C
1	TRACE	ACCOUNT	AMOUNT
2	1	111	100
3	2	222	200
4	3	333	300
5	4	444	400
6	5	555	500
7	5	555	500
Sheet2

Desired Result:

Book1
	I	J	K
1	TRACE	ACCOUNT	AMOUNT
2	5	555	500
Sheet2

Is the only way is to group and left join on trace, account, amount and count or is there an optimal way?

[/COLOR]SELECT Table1.TRACE, Table1.ACCOUNT, Table1.AMOUNT, Count(Table1.AMOUNT) AS CountOfAMOUNT
FROM Table1
GROUP BY Table1.TRACE, Table1.ACCOUNT, Table1.AMOUNT;

SELECT Table2.TRACE, Table2.ACCOUNT, Table2.AMOUNT, Count(Table2.AMOUNT) AS CountOfAMOUNT
FROM Table2
GROUP BY Table2.TRACE, Table2.ACCOUNT, Table2.AMOUNT;

SELECT Table2_Count.*
FROM Table2_Count LEFT JOIN Table1_Count ON (Table2_Count.TRACE = Table1_Count.TRACE) AND (Table2_Count.ACCOUNT = Table1_Count.ACCOUNT) AND (Table2_Count.AMOUNT = Table1_Count.AMOUNT) AND (Table2_Count.CountOfAMOUNT = Table1_Count.CountOfAMOUNT)
WHERE (((Table1_Count.CountOfAMOUNT) Is Null));

Result:

Book1
	M	N	O	P
2	TRACE	ACCOUNT	AMOUNT	CountOfAMOUNT
3	5	555	500	2
Sheet2

Thank you for any help on this, I am kind of a newbie with Access so any suggestions would be great!

 

[Anand Sharma]

Simply Go to Data Tab -> Remove Duplicate, select the column/columns on which duplicate criteria is based on.


Final Table would be without duplicate