duplicates data across 3 fields

  • Thread starter Thread starter gil_wilkes
  • Start date Start date
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.
 
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!
 
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.
 
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)
 
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.
 
Back
Top