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
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|