A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

duplicates data across 3 fields



 
 
Thread Tools Display Modes
  #1  
Old March 1st, 2010, 08:58 PM posted to microsoft.public.access.queries
gil_wilkes
external usenet poster
 
Posts: 20
Default duplicates data across 3 fields

How can i find duplicate data across 3 fields.

Thanks in advance.
  #2  
Old March 1st, 2010, 10:07 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default 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  
Old March 1st, 2010, 10:12 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old March 1st, 2010, 10:39 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old March 2nd, 2010, 10:20 PM posted to microsoft.public.access.queries
Gilbo
external usenet poster
 
Posts: 18
Default 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  
Old March 2nd, 2010, 10:36 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old March 2nd, 2010, 10:42 PM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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  
Old March 3rd, 2010, 06:20 PM posted to microsoft.public.access.queries
gil_wilkes
external usenet poster
 
Posts: 20
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.