Partial Match Query

[punna111]

Hi all,

I need a Partial Match Query in Access, How can i do that?
I am only getting if that matches exactly. I tried with Inner Joins.
If name is AccessForums.net in one table and other table i have AccessForums net or Access forums dot net.


I need query that should pull these records also matching with my name.

[orange]

Do you have some real sample data? 3 or 4 records should do

[June7]

No matter what, probably won't catch all. That's what happens with bad data.

Could try matching on so many characters. How many is enough or too much is subjective. Would it help if there are no spaces?

SELECT Table1.*, Table2.* FROM Table1, Table2 WHERE Table1.Field LIKE Left(Replace(Table2.Field, " ", ""), 10) & "*";

Otherwise, build a VBA custom function.

Pattern matching has been discussed numerous times in forum. Search for threads.

[punna111]

Hi,

Sorry for the missing/less information
Please find the attached excel file. Its just a sample file. and Sample data. there are many scenarios like this but i have given some scenarios to you guys.

There we have two tables. I want to match Name and zip.
Zip field should be 100% match. But Name should be partial match, If you see the names particularly all are same, However there is some different type of characters and words used.
If i run a query how can i get that all these are matching with those names.

[orange]

Where does Z No fit in?
Typically in a scenario like this, the objective is get to one(1) authoritative table and to use it or a read only copy in all related applications.
As June7 said it may not (usually won't) happen in 1 algorithm. You may get 80 % with some prudent code, but the next 20% may take several algorithms and tries.
The real key is to get to a unique recordId and then the proper spelling/format/name and any acceptable aliases.

It really depends on you real needs.

eg:
MyAuthoritativeTable
PersonID
PersonName (I wouldn't suggest both First and Last Name, and Salutation in one field -- use atomic fields)

MyPeopleAliasTable
AliasID PK
PersonId fields in green make up a composite unique index This is to prevent duplicates
AlaisName


You could take 5 characters from records in table1 and look for matches in table2. (Change 5 to whatever you want but start with some number and see what the hit count is.
You could match on zip and list the Names alphabetically and eyeball the list in order to build an algorithm.

There are many posts and articles on the net dealing with pattern matching.

What exactly do you want in your final result, and where and how do you plan to use it. As I mentioned, this is usually done to get a single authoritative table [ a reference or corporate table for sharing with strict rules and controls for editing. It is not something you would do repeatedly.

[punna111]

Thanks for the reply, but I am not able to understand your reply exactly (100 %). Maybe i am not familiar with that terminology. But I really need to learn Access and SQL (very much).

I do not match Z No. All times it will be vary, here i have given just example. but we shouldn't think about Z No. here.
There is no way to get a unique id here. we have match with something. Actually its a manual match where i need to check for the names in a data base with different type of criteria.

We cannot Eyeball the list as i need to match one record with more than 3,000,000 records, Of course i am not sure how many records will match for that name & zip.
Please give me the link for pattern matching.

i want to match name and zip code. as i have given in the attachment for the scenarios.
If you feel that i didn't answer any of your query, it means i didn't understand it.
Let me know your skype id to discuss on this more. (I am not sure whether we can share our id's or not)

[June7]

Did you try the query suggested in post 3? With 3,000,000 records it might take all day!

[punna111]

Hi,

I didnt try.. becoz i didn't understand it.

[orange]

What exactly in post 3 do you not understand?

You say you have 3,000,000 records. Have you never considered removing duplicates or standardizing the format or building an authoritative, reference list?

How do you process these today?

[punna111]

I didnt understand below lines.

"Could try matching on so many characters. How many is enough or too much is subjective. Would it help if there are no spaces?

SELECT Table1.*, Table2.* FROM Table1, Table2 WHERE Table1.Field LIKE Left(Replace(Table2.Field, " ", ""), 10) & "*";

Otherwise, build a VBA custom function."

We shouldn't remove duplicates or standardize the 3,00,000 records. (I am sorry i have used an extra zero in the previous posts, it is actually 3 Lakh records).
Becoz those are added previously. so now we have to take care about the new records.
Presently I will check every record with one of my tool. I will give search creteria if i found any duplicates then i will take the next action.
it is very difficult to check every record manually, so i want to use a query.

[June7]

Okay, 3 lakh = 300,000 (3,00,000 in parts of Indian subcontinent - thank you Wikipedia)

Suggest you try building query per the example given and see what you think of the results.