query to check pattern in name

[johnmerlino]

Hey all,

I have a record that looks like this:
APEL,RICHARD & RHINA

Notice the ampersand before the first name and notice there's another name after the last name, which is Apel. So I would like to account for this pattern. I come up with this:

INSERT INTO possibles ( fullname )
SELECT Trim(Trim(temp_table.last_name) & "," & IIf(Trim(middle_initial) Is Null,"* &" & Trim(temp_table.first_name),"* &" & Trim(temp_table.first_name) & " ") & IIf(Trim(middle_initial) Is Null,"",Trim(middle_initial))) AS Expr1
FROM temp_table;

But this could be pulling unnessary records, that is, records that don't match the pattern of the name I used as an example above. Any suggestions on how to refine the query?
Also Note it needs to account for middle names and that's why I included middle_initial field and if condition above.
Thanks for response.

 

[John Big Booty]

You Could use the InStr() function in the Criteria of your query to detect the presence of the comma and ampersand;

InStr([FieldName],",")<>"0" And InStr([FieldName],"&")<>"0"

 

[johnmerlino]

Problem is detecting for this very exact match:
APEL,RICHARD & RHINA
That RICHARD is a variable. It can be any name. I just need to make sure that the query I posted in the initial question accomodates ONLY this specific pattern and not any other pattern. Hence, the pattern is [lastname][comma][variablename][space][ampersand][space][firstname] if [middleinitial] IS NULL[space]. Else [lastname][comma][variablename][space][ampersand][space][firstname]if[middleinitial] is not null[middleinitial]
where variable name is an arbitrary name that's not first, last, or middle initial. In otherwords, it's the husband or wife of the name I am searching.

In otherwords, these are the matches I am looking for:
APEL,RICHARD & RHINA
APEL,RICHARD & RHINA B
APEL,RICHARD B & RHINA
APEL,RICHARD B & RHINA B
APEL,RICHARD Bud & RHINA B
APEL,RICHARD B & RHINA Ann

 

[Brianwarnock]

Sorry but could you explain why Big John's suggestion does not work, it will return any case of the field having both a , and an &

Brian

 

[johnmerlino]

So big john solution would accomodate this:
APEL,RICHARD & RHINA
APEL,RICHARD & RHINA B
APEL,RICHARD B & RHINA
APEL,RICHARD B & RHINA B
APEL,RICHARD Bud & RHINA B
APEL,RICHARD B & RHINA Ann

But not something like thse:
APEL,RICHARD & RHINA LIV H/E
APEL,RHINA
APEL,RHINA & RICHARD

If it accomodates the first list and not the last list or any other kind of variable, then that would be great. I'm just not sure that it's doing it. But I am not an expert. So I may be wrong. Thanks for response.

 

[Brianwarnock]

It will not select the 2nd in list 2, I do not see how the 1st and 3rd differ in pattern from the data in list 1.

Brian

 

[johnmerlino]

I append the following to the possibles table, which basically are a bunch of pattern checks so when I compare two tables, it checks for patterns in another table and pulls those records if there is a pattern match. Now all the queries below are pretty much similar, just accounting for different situations:

[FONT=Arial]SELECT trim(Trim(foreclosure_defense_final.last_name) & " " & Trim(foreclosure_defense_final.first_name) & " " & iif(Trim(middle_initial) is null, "", Trim(middle_initial))) AS fullname INTO possibles[/FONT]
[FONT=Arial]FROM temp_table;[/FONT]

[FONT=Arial]INSERT INTO possibles ( fullname )[/FONT]
[FONT=Arial]SELECT Trim(Trim(temp_table.last_name) & "," & IIf(Trim(middle_initial) Is Null,Trim(temp_table.first_name),Trim(temp_table.first_name) & ",") & IIf(Trim(middle_initial) Is Null,"",Trim(middle_initial))) AS Expr1[/FONT]
[FONT=Arial]FROM temp_table;[/FONT]

[FONT=Arial]INSERT INTO possibles ( fullname )[/FONT]
[FONT=Arial]SELECT Trim(Trim(temp_table.last_name) & ", " & IIf(Trim(middle_initial) Is Null,Trim(temp_table.first_name),Trim(temp_table.first_name) & ",") & IIf(Trim(middle_initial) Is Null,"",Trim(middle_initial))) AS Expr1[/FONT]
[FONT=Arial]FROM temp_table;[/FONT]

[FONT=Arial]INSERT INTO possibles ( fullname )[/FONT]
[FONT=Arial]SELECT Trim(Trim(temp_table.last_name) & "," & IIf(Trim(middle_initial) Is Null,Trim(temp_table.first_name),Trim(temp_table.first_name) & ", ") & IIf(Trim(middle_initial) Is Null,"",Trim(middle_initial))) AS Expr1[/FONT]
[FONT=Arial]FROM temp_table;[/FONT]

[FONT=Arial]INSERT INTO possibles ( fullname )[/FONT]
[FONT=Arial]SELECT Trim(Trim(temp_table.last_name) & "," & IIf(Trim(middle_initial) Is Null,Trim(temp_table.first_name),Trim(temp_table.first_name) & " ") & IIf(Trim(middle_initial) Is Null,"",Trim(middle_initial))) AS Expr1[/FONT]
[FONT=Arial]FROM temp_table;[/FONT]

[FONT=Arial]INSERT INTO possibles ( fullname )[/FONT]
[FONT=Arial]SELECT Trim(Trim(temp_table.last_name) & " " & IIf(Trim(middle_initial) Is Null,Trim(temp_table.first_name),Trim(temp_table.first_name) & ",") & IIf(Trim(middle_initial) Is Null,"",Trim(middle_initial))) AS Expr1[/FONT]
[FONT=Arial]FROM temp_table;[/FONT]

So basically I append the above pattern matches to possibles before I run this check on possibles:

[FONT=Arial]SELECT contacts.id, contacts.names_1, contacts.names_2, contacts.addresses INTO PrepareForDuplicateCheck[/FONT]
[FONT=Arial]FROM contacts, possibles[/FONT]
[FONT=Arial]WHERE (INSTR(CONTACTS.NAMES_1, possibles.fullname) > 0)[/FONT]
[FONT=Arial]Or[/FONT]
[FONT=Arial](INSTR(CONTACTS.NAMES_2, possibles.fullname) > 0) [/FONT]
[FONT=Arial]and[/FONT]
[FONT=Arial]([/FONT]
[FONT=Arial]CONTACTS.us_states_and_canada = "FL"[/FONT]
[FONT=Arial]or[/FONT]
[FONT=Arial]CONTACTS.us_states_and_canada = "NY"[/FONT]
[FONT=Arial]);[/FONT]

Now unfortunately what I append doesn't accomodate for these patterns:
APEL,RICHARD & RHINA
APEL,RICHARD & RHINA B
APEL,RICHARD B & RHINA
APEL,RICHARD B & RHINA B
APEL,RICHARD Bud & RHINA B
APEL,RICHARD B & RHINA Ann


And I was just looking to make sure the solution could assimilate into what I already done here. So I could just add it as another append query. Thanks for response.

 

[johnmerlino]

For the 1st, the reason why I don't want to return "LIV" or "H/E" is because those are not middle initials or middle names. And I don't want the search thinking they are. For the 3rd, this pattern I am already accomodating for in another query I posted above: APEL,RHINA & RICHARD

 

[Brianwarnock]

The question I am asking is how are these different

APEL,RICHARD & RHINA
APEL,RHINA & RICHARD

or even

APEL,RICHARD B & RHINA Ann
APEL,RICHARD & RHINA LIV H/E

so that you would want one but not the other.


Brian

Ah! I see that our posts crossed, but how can the system know that LIV is not a name? Plus how does it know that you have already selected a totally different record that we can see is the same as the current one?

 

[johnmerlino]

Thanks for response. In one of my posts above, I pasted a number of "INSERT INTO " statements that almost serve as a regular expression for patterns. These statements work for most of my queries. However, somehow these ones:
APEL,RICHARD & RHINA
APEL,RICHARD & RHINA B
APEL,RICHARD B & RHINA
APEL,RICHARD B & RHINA B
APEL,RICHARD Bud & RHINA B
APEL,RICHARD B & RHINA Ann

Escape the regular expression type of "Insert Into" queries I defined above. So basically I am just looking for another "insert into" query that will check for a common pattern in the names above.

I don't know why I mentioned the "LIV" or "H/E" thing above. That's not really important. To answer your question, if it pulls the first and third that's fine. I just wouldn't want it to pull more records than in needs to. I already have a pattern for the third match anyway in the "Insert Into" statemens I posted above.

Thanks for response.

 

[Brianwarnock]

If pulling with several different criteria it will always be possible that a record may match more than one, if the receiving table can be set up to avoid duplicates then it should be ok.

Brian

 

[johnmerlino]

ok, so going back to my initial question, do you think this will accomodate the patterns I described:

INSERT INTO possibles ( fullname )
SELECT Trim(Trim(temp_table.last_name) & "," & IIf(Trim(middle_initial) Is Null,"* &" & Trim(temp_table.first_name),"* &" & Trim(temp_table.first_name) & " ") & IIf(Trim(middle_initial) Is Null,"",Trim(middle_initial))) AS Expr1
FROM temp_table;

It's this part:
"* &"
that concerns me.

Or should I do this, as recommended by john big:

INSERT INTO possibles ( fullname )
SELECT Trim(Trim(temp_table.last_name) & "," & IIf(Trim(middle_initial) Is Null, temp_table.first_name, InStr([temp_table.first_name & temp_table.middle_initial],"&")<>"0" & IIf(Trim(middle_initial) Is Null,"",
InStr([first_name & middle_initial],"&")<>"0"))) AS Expr1
FROM temp_table;

Can I somehow get that first_name and middle_initial into InStr?

Thanks for response.