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



 
 
Thread Tools Display Modes
  #1  
Old March 2nd, 2010, 06:26 PM posted to microsoft.public.access.queries
gil_wilkes
external usenet poster
 
Posts: 20
Default Duplicates

In a query how can I check for duplicates in 3 different fields. The
duplicates could be in field 1 and field 2, field 1 and field 3 or field 2
and field 3 or Field 1 and field 2 and field 3. I can find duplicates in any
one field, but not in a combination of others.
Thanks for your help in advance.
  #2  
Old March 2nd, 2010, 06:58 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Duplicates

Do you mean like this --
Cust Item Type QTY
Cust1 Beans Pinto 5
Cust1 Beans Pinto 5
Cust1 Beans Pinto 5

or this --
Cust1 Beans Pinto 5
Cust1 Beans Lima 2
Cust1 Beans Kidney 3

If as in the first use this --
SELECT Cust, Item, Type, QTY, Count([Cust]) AS CountGroup
FROM YourTable
GROUP BY Cust, Item, Type, QTY
WHERE Count([Cust]) 1;

--
Build a little, test a little.


"gil_wilkes" wrote:

In a query how can I check for duplicates in 3 different fields. The
duplicates could be in field 1 and field 2, field 1 and field 3 or field 2
and field 3 or Field 1 and field 2 and field 3. I can find duplicates in any
one field, but not in a combination of others.
Thanks for your help in advance.

  #3  
Old March 2nd, 2010, 09:13 PM posted to microsoft.public.access.queries
Gilbo
external usenet poster
 
Posts: 18
Default Duplicates

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)

"KARL DEWEY" wrote:

Do you mean like this --
Cust Item Type QTY
Cust1 Beans Pinto 5
Cust1 Beans Pinto 5
Cust1 Beans Pinto 5

or this --
Cust1 Beans Pinto 5
Cust1 Beans Lima 2
Cust1 Beans Kidney 3

If as in the first use this --
SELECT Cust, Item, Type, QTY, Count([Cust]) AS CountGroup
FROM YourTable
GROUP BY Cust, Item, Type, QTY
WHERE Count([Cust]) 1;

--
Build a little, test a little.


"gil_wilkes" wrote:

In a query how can I check for duplicates in 3 different fields. The
duplicates could be in field 1 and field 2, field 1 and field 3 or field 2
and field 3 or Field 1 and field 2 and field 3. I can find duplicates in any
one field, but not in a combination of others.
Thanks for your help in advance.

  #4  
Old March 2nd, 2010, 10:39 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Duplicates

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)

"KARL DEWEY" wrote:

Do you mean like this --
Cust Item Type QTY
Cust1 Beans Pinto 5
Cust1 Beans Pinto 5
Cust1 Beans Pinto 5

or this --
Cust1 Beans Pinto 5
Cust1 Beans Lima 2
Cust1 Beans Kidney 3

If as in the first use this --
SELECT Cust, Item, Type, QTY, Count([Cust]) AS CountGroup
FROM YourTable
GROUP BY Cust, Item, Type, QTY
WHERE Count([Cust]) 1;

--
Build a little, test a little.


"gil_wilkes" wrote:

In a query how can I check for duplicates in 3 different fields. The
duplicates could be in field 1 and field 2, field 1 and field 3 or field 2
and field 3 or Field 1 and field 2 and field 3. I can find duplicates in any
one field, but not in a combination of others.
Thanks for your help in advance.

  #5  
Old March 3rd, 2010, 06:22 PM posted to microsoft.public.access.queries
gil_wilkes
external usenet poster
 
Posts: 20
Default Duplicates

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)

"KARL DEWEY" wrote:

Do you mean like this --
Cust Item Type QTY
Cust1 Beans Pinto 5
Cust1 Beans Pinto 5
Cust1 Beans Pinto 5

or this --
Cust1 Beans Pinto 5
Cust1 Beans Lima 2
Cust1 Beans Kidney 3

If as in the first use this --
SELECT Cust, Item, Type, QTY, Count([Cust]) AS CountGroup
FROM YourTable
GROUP BY Cust, Item, Type, QTY
WHERE Count([Cust]) 1;

--
Build a little, test a little.


"gil_wilkes" wrote:

In a query how can I check for duplicates in 3 different fields. The
duplicates could be in field 1 and field 2, field 1 and field 3 or field 2
and field 3 or Field 1 and field 2 and field 3. I can find duplicates in any
one field, but not in a combination of others.
Thanks for your help in advance.

 




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 09:57 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.