Find Duplicates

[aroma658]

Hi,

I am new to this forum and I am looking for some help. I have attached the sample database to avoid confusion and make myself as much clear.

I have got two different tables named Sample data and Company Data. I want to run a query to find duplicates. I want to find a partial duplicates. i.e I want to look for words or numbers in Sample Data Table and find a match in Company Table.

For example if I look for 71118 in Sample data it should return White & Dark 71118 and indicate that this number is present in this.

Can anyone please help me? I have tried instring but its not giving me the desired result.

Thanks in advance,
aroma658

 

[maxmangion]

you may try something like the following:

SELECT table1.field1, table2.field1
FROM table1 INNER JOIN table2 ON table1.field1 = table2.field1
GROUP BY table1.field1, table2.field1
HAVING (((table1.field1) Like "*" & [Enter Number] & "*") AND ((table2.field1) Like "*" & [Enter Number] & "*"));

Try replacing the above with the actual table and field names.

 

[aroma658]

Hi Max,


Thanks for the reply. I did run the query and it works for text but not for numbers. I tested by entering number 71118 and it doesnt find any duplicates though it does exist.

Can you shed some light on this?

Thanks a lot
Aroma

 

[SimonB1978]

Hi,

Use this syntax for numbers (as long as you know exactly what they are). If I'm not mistaken, "LIKE" will only work for text.

((table1.field1) = [Enter Number]) ...

Simon B.