query giving unexpected result

[johnmerlino]

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.

 

[WayneRyan]

J,

You're getting a Cartesian product. You need to join contacts & temp_query on some field(s).

SELECT contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
FROM contacts, temp_query
WHERE [B][SIZE="3"]contacts.PK = temp_query.PK And[/SIZE][/B] 
          ((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] & "*"));

Wayne