Duplicates

  • Thread starter Thread starter gil_wilkes
  • Start date Start date
G

gil_wilkes

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

KARL DEWEY said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help, it now does what I want it to do.



KARL DEWEY said:
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 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)

KARL DEWEY said:
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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top