If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
duplicates data across 3 fields
How can i find duplicate data across 3 fields.
Thanks in advance. |
#2
|
|||
|
|||
duplicates data across 3 fields
gil_wilkes wrote:
How can i find duplicate data across 3 fields. Thanks in advance. 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. -- HTH, Bob Barrows |
#3
|
|||
|
|||
duplicates data across 3 fields
On Mon, 1 Mar 2010 11:58:03 -0800, gil_wilkes
wrote: How can i find duplicate data across 3 fields. Thanks in advance. 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! -- John W. Vinson [MVP] |
#4
|
|||
|
|||
duplicates data across 3 fields
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. -- Duane Hookom Microsoft Access MVP "gil_wilkes" wrote: How can i find duplicate data across 3 fields. Thanks in advance. |
#5
|
|||
|
|||
duplicates data across 3 fields
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) "John W. Vinson" wrote: On Mon, 1 Mar 2010 11:58:03 -0800, gil_wilkes wrote: How can i find duplicate data across 3 fields. Thanks in advance. 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! -- John W. Vinson [MVP] . |
#6
|
|||
|
|||
duplicates data across 3 fields
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. -- Build a little, test a little. "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) "John W. Vinson" wrote: On Mon, 1 Mar 2010 11:58:03 -0800, gil_wilkes wrote: How can i find duplicate data across 3 fields. Thanks in advance. 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! -- John W. Vinson [MVP] . |
#7
|
|||
|
|||
duplicates data across 3 fields
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 |
#8
|
|||
|
|||
duplicates data across 3 fields
Thanks for your help, it now does what I want it to do.
"KARL DEWEY" wrote: 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. -- Build a little, test a little. "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) "John W. Vinson" wrote: On Mon, 1 Mar 2010 11:58:03 -0800, gil_wilkes wrote: How can i find duplicate data across 3 fields. Thanks in advance. 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! -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|