Gilbo wrote:
What i want is to find duplicates like this-

Field1 Field2 Field3
cust1 cust2 cust3
cust4 cust5 cust6
cust2 cust7 cust8 (dupe cust2)
cust9 cust4 cust10 (dupe cust4)
cust11 cust3 cust12 (dupe cust3)
cust2 cust2 cust 1 (dupe cust1)
cust13 cust6 cust14 (dupe cust6)

How can i find duplicate data across 3 fields.

If you can fix this, I would do it. This is the problem with an improperly
normalized design. You can't look in *one* place for the fact you're looking
for.

SELECT Field1
FROM Table1
UNION
SELECT Field2
FROM Table1
UNION
SELECT Field3
FROM Table1;

UNION by definition automatically removes duplicates. If you want to keep
them, use UNION ALL instead of UNION.

Then you can do a summary query (Count) on the field(s) you're looking for
duplicates on. Keep in mind that a union query cannot use table indexes, so
performance is absolutely awful.

If you can, fix the problem at the table level (redesign your table(s)).
Then you can index and have fast queries. I had to write very large union
queries at a job once, and performance is horrendous. Avoid it if you can.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201003/1