returning records that match the characters of another field in access

[johnmerlino]

Hey all,



This statement is flawed:

SELECT contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
FROM contacts, temp_query
WHERE 
(
contacts.names_1  Like "*" & temp_query.first_name    & "*" 
and 
contacts.names_1  Like "*" & temp_query.last_name    & "*" 
 
)
OR
(
contacts.names_2  Like "*" & temp_query.first_name    & "*" 
and 
contacts.names_2  Like "*" & temp_query.last_name    & "*" 
)
;

If names_1 has the string doodle and if temp_query first_name or last_name has the string doo, that doodle will be returned because of the like clause. I would only want record to be returned if it had doo, not doodle.

My records could look like this in names_1 or names_2:
Clark,Kelly Smith, Sam
Clark Kelly Smith, John
I don't want to return anything like
Clarkson,Kelly Smith, Sam
Clark, Kellylin Smith, Sam

Hence, since I don't want to check for Smith, John but I want to check for Clark, Kelly or Clark Kelly, I can't check for exact match but rather string pattern:

SELECT contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
FROM contacts, temp_query
WHERE 
(
contacts.names_1  Like temp_query.first_name    & " " 
and 
contacts.names_1  Like temp_query.last_name    & "," 
 
)
OR
(
contacts.names_2  Like temp_query.first_name    & " " 
and 
contacts.names_2  Like temp_query.last_name    & " " 
)
;

However, this returns 0 records, even though there should at least be 80 returned records.

Thanks for any response.

[johnmerlino]

Problem is that the field won't contain an exact match it may contain 5 names and I am only searching for a specific first and last name within those five names but I know that for first name, there will always be a space after it and for last name there will either be a space or comma.

[johnmerlino]

I tried this:

SELECT contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
FROM contacts, temp_query
WHERE
(
contacts.names_1  = '" & temp_query.first_name    & "'
and
contacts.names_1  = '" & temp_query.last_name    & "'
 
)
OR
(
contacts.names_2  = '" & temp_query.first_name    & "'
and
contacts.names_2  = '" & temp_query.last_name    & "'
);

But this returns 0 records. Problem is names_1, for example, will contain the first and last name I need but can also contain additional names so I cannot check the actual field for an exact match I have to search the field where I can find a pattern, such as first name having space after it and last name having space or comma after it.

[weekend00]

Please try if doo is in the left of the names.( doo can match "doo john", but not match "a doo john", if you also want "a doo john", please let me know and i will give next version )

SELECT contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
FROM contacts, temp_query
WHERE
(
contacts.names_1 Like temp_query.first_name & " *"
and
contacts.names_1 Like temp_query.last_name & " *"

)
OR
(
contacts.names_2 Like temp_query.first_name & " *"
and
contacts.names_2 Like temp_query.last_name & " *"
)
;

[johnmerlino]

This returns 0 records as well:

SELECT contacts.names_1,contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
FROM contacts, temp_query
WHERE ( 
contacts.names_1  Like '& temp_query
.first_name    &  ""'  
and  
contacts.names_1  Like '&  temp_query
.last_name    & ""'  
 
) 
OR 
( 
contacts.names_2  Like '&  temp_query
_query.first_name    & "" '
and  
contacts.names_2 Like '&  temp_query
_query.last_name    & "," '
);

[weekend00]

Please try my version

[johnmerlino]

Thanks for reply. That returned 0 matches as well.

Here's what some of records look like:
JONES,PATRICK
MILES,JAMIE & MARLON D
LEMAY,PAUL H
PATRICK MICHAE LLC
PINTO,MAX & MICHAL

Note the first example I gave had no spaces, when it's just first and last name of one person, it has no spaces. But in second example, I may need to check for Marlon Miles even though it's worded as "
MILES,JAMIE & MARLON D".

[weekend00]

that's much more complicated, we need to match these cases: (e.g. for "Jack")
1 "jack"
2 "jack *"
3 "jack,*"
4 "* Jack"
5 "* Jack *"
6 "* Jack,*"
7 "*,Jack"
8 "*,jack *"
9 "*,Jack,*"

and we need to match firstname with lastname

Did I miss any case?

[johnmerlino]

The first and last will be in the field somewhere like in the example I gave. Although I don't think the first name will ever have a comma in front of it, but the last name may or may not have a comma in front and there's no space in front of the comma and the first name is most likely right after the comma without a space. However, whenever the field contains "TR", "LIV" or "REV", then there will be no comma after last name or before first name. When you have "Jack ", is that saying that it is to the farthest left of the cell, so "*Jack" won't work if it's to the farthest left to the cell?

[johnmerlino]

This returned 50 records even though it should have returned 80:

SELECT contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes

FROM contacts, temp_query
WHERE (
contacts.names_1 Like temp_query.first_name & " *"
or
contacts.names_1 Like "*," & temp_query.first_name 
or
contacts.names_1 Like "* " & temp_query.first_name 
)
and 
(
contacts.names_1 Like temp_query.last_name & ",*" 
or
contacts.names_1 Like temp_query.last_name & " *" 
or
contacts.names_1 Like  "* " & temp_query.last_name
)
OR
(
contacts.names_2 Like temp_query.first_name & " *"
or
contacts.names_2 Like "*," & temp_query.first_name 
or
contacts.names_2 Like "* " & temp_query.first_name 
)
and 
(
contacts.names_2 Like temp_query.last_name & ",*" 
or
contacts.names_2 Like temp_query.last_name & " *" 
or
contacts.names_2 Like  "* " & temp_query.last_name
);

[weekend00]

I did not fully get you. But you can modify base on following query:

SELECT contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
FROM contacts, temp_query
WHERE
( names_1 Like "*[ ,]" & first_name & ""
OR names_1 Like "*[ ,]" & first_name & "[ ,]*"
OR names_1 Like first_name & "[ ,]*"
OR names_1 = first_name
)
and
( names_2 Like "*[ ,]" & last_name & ""
OR names_2 Like "*[ ,]" & last_name & "[ ,]*"
OR names_2 Like last_name & "[ ,]*"
OR names_2 = last_name
)
or
( names_2 Like "*[ ,]" & first_name & ""
OR names_2 Like "*[ ,]" & first_name & "[ ,]*"
OR names_2 Like first_name & "[ ,]*"
OR names_2 = first_name
)
and
( names_1 Like "*[ ,]" & last_name & ""
OR names_1 Like "*[ ,]" & last_name & "[ ,]*"
OR names_1 Like last_name & "[ ,]*"
OR names_1 = last_name
)

[johnmerlino]

For first name, basically, there could be:
* a space before and after first name
* a comma before first name and space after first name
* a comma before first name and nothing after it
For last name, basically, there could be:
* a space before and after last name
* a comma after last name and space before last name
* a comma after last name and nothing before last name
Note when there is a comma, there is never space before or after it
Given these conditions, I am going to try this:

SELECT contacts.id, contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
FROM contacts, temp_query
WHERE 
( 
names_1 Like "*[,]" & first_name & "" 
or
names_1 Like "" & first_name & ""
or
names_1 Like "" & first_name
)
and
( 
names_1 Like last_name & "[,]*" 
Or
names_1 Like "" & last_name & ""
or
names_1 Like last_name & ""
)
OR
 
( 
names_2 Like "*[,]" & first_name & "" 
or
names_2 Like "" & first_name & ""
or
names_2 Like "" & first_name
)
 
)
or
( 
names_2 Like last_name & "[,]*" 
Or
names_2 Like "" & last_name & ""
or
names_2 Like last_name & ""
)

Note this query does take 15 minutes iterating through 730,000 records.

And then I have to throw in there this:

IF (temp_query.middle_initial IS NOT NULL) THEN
(
contacts.names_1 Like "" & temp_query.middle_initial & "" 
or
contacts.names_1 Like "" & temp_query.middle_initial
)
And
(
contacts.names_2 Like "" & temp_query.middle_initial & "" 
or
contacts.names_2 Like "" & temp_query.middle_initial
)
END IF
AND
(
contacts.us_states_and_canada == “FL” or “NY”
);

[weekend00]

My concerns about you last query:
I think you want " " instead (space inside" ") for all occurrances:

SELECT contacts.id, contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
FROM contacts, temp_query
WHERE 
( 
names_1 Like "*[,]" & first_name & ""  
or
names_1 Like "" & first_name & ""
or