Microsoft Access SQL query matching names unexpectedly

Question

Hey all,

I tried using Access for queries and it returns extremely unexpected results (where it would return records from contacts table that didn't even appear in the temp_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 
   (((temp_query.first_name) Like "*" & [contacts].[names_1] & "*") 
       AND ((temp_query.last_name) Like "*" & [contacts].[names_1] & "*")) 
  OR 
   (((temp_query.first_name) Like "*" & [contacts].[names_2] & "*") 
       AND ((temp_query.last_name) Like "*" & [contacts].[names_2] & "*"));

I thought the above would make sense since the strings in the first_name and last_name of temp_query can be in name_1 of contacts and the the first_name and last_name of temp_query can be in name_2 of contacts . But I don't think first_name can be in name_1 and last_name in name_2 and visa versa.

Someone recommended this, but this ultimately only returned a single record where it should have at least returned 70 (not to mention it took 35 minutes for the query to bring the result):

Criteria: (in first_name column): Like "*" & [contacts].[name1] & "*"  (in last_name column) like "*" & [[contacts].[name2] & "*"
Or:       (in first_name column): Like "*" & [contacts].[name2] & "*"  (in last_name column) Like "*" & [[contacts].[name1]  & "*"

So I'm not sure what shoud be the right criteria for the intended effect.

Thanks for any response.

Answer 1

Do you know SQL? It would probably be better to open up the query in SQL view and just edit it there.

Then, when you go back to design view, the fields in the design view will reflect what you put in SQL view automatically to see what it was that you were missing.

Answer 2

I think this is what you are looking for. You can just paste this into your SQL View

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    & "*" 
)
;

Your original where clause was looking for when contacts.name_1 was in first_name and in last_name or if contacts.name_2 was in first_name and in the last_name.