duplicates data across 3 fields

B

Bob Barrows

gil_wilkes said:
How can i find duplicate data across 3 fields.

Thanks in advance.
Click to expand...
Create a grouping (totals) query that groups by the three fields, then
limit the results to those where count(*)>1. The sql would look lkie
this:

select field1,field2,field3,count(*) as RecsPerGroup
from tablename
group by field1,field2,field3
having count(*) > 1

Create a new query in design view, close the Choose Tables dialog
without selecting a table, immediately switch your query to SQL View
(toolbar button, right-click menu, or View menu), paste in the above
statement, fix the table and field names and run it. Switch back to
Design View to see how to build the query in the QBE.
 
J

John W. Vinson

How can i find duplicate data across 3 fields.

Thanks in advance.
Click to expand...

You'll need to ask a clearer question, perhaps with an example. Duplicates...
within the fields? across records in a table? duplicates between two tables?

More info please!
 
D

Duane Hookom

Without more information such as table and field names, sample records,
desired output, etc; I can only suggest you print them out, apply your
specifications on what is a duplicate across 3 fields and highlight them with
a marker.

Are you looking within single records? Do all three field have to be exactly
the same? Please provide more information.
 
G

Gilbo

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)
 
K

KARL DEWEY

Create a union query to get your data into one field and then run a totals
query counting how many times an item appears in the record set with a
criteria of >1.
 
P

PieterLinden via AccessMonster.com

Gilbo said:
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)
Click to expand...

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.
 
Top