Duplicate values in one table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Duplicate values in one table

Author

Topic

jacque
Starting Member

6 Posts

Posted - 2003-10-14 : 17:35:51

We have created a table that has a listing of MANY email addresses from where we get spam. We are trying to block these email addresses. As you are aware these people change their address by just a little and it gets through. We are trying to go through the list and determine the similarities. I created a query that will take a certain amount of characters on one side of the '.'. I want to compare this field with the original field that has the full address.

I have 4,951 items in the DB and everything I have tried will compare each item 4,951 times. Therefore my Access times out. Here are a couple of the queries that I have tried, neither of them work.


SELECT Table1.Field1, AllItems.ShortField1
FROM Table1, AllItems
WHERE (('*' &(AllItems.ShortField1) & '*' Not like ( [Table1].[Field1])));


SELECT Table1.Field1, AllItems.ShortField1
FROM Table1, AllItems
WHERE (('*' &(AllItems.ShortField1) & '*' like ( [Table1].[Field1])));

Table1 is the original table with full addresses. AllItems.ShortField1 is the shortened field.
Can anyone help?

This is in Access97

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-15 : 13:30:20

WHERE

GreaterField Not Like "*" & ShorterField & "*"