Query showing unexpected results

[johnmerlino]

Hey all,

I had my query getting closer to being accurate until I had to make some additional changes. One change was if in our temp_query, the middle_name field had a value in it and therefore was not null, then we use the sql like to find like characters in the print_ready query that match somewhere in the field the first_name, last_name, and middle_initial of the temp_query (note that for the fields, there will usually be other names as well and hence I had to use Like statement). Else, if there was no middle initial, then we don't query the middle_name, since it does not exist for that specific record. In addition, I had to ignore certain characters during a selection so as not to confuse them with middle names. In order to ignore them when making a selection, I used the REPLACE(). Actually it returns less records than it should. It should return 80 and it's returning 66.

 SELECT print_ready.id, print_ready.names_1, print_ready.names_2,  print_ready.addresses, print_ready.cities,  print_ready.us_states_and_canada, print_ready.zip_codes
  FROM print_ready, temp_query
  WHERE (
  (
  REPLACE(REPLACE(REPLACE(print_ready.names_1,'LIV',''),'REV',''),'TR','')
  ) 
 and
  ( 
 print_ready.us_states_and_canada = "FL"
  or
  print_ready.us_states_and_canada = "NY"
  ) 
 and
  IIF ((temp_query.middle_initial IS NOT NULL),
  (
  (
  print_ready.names_1 Like "*," & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " " & temp_query.middle_initial
  )
  and 
  (
  print_ready.names_1 Like temp_query.last_name & ",*" 
  or
  print_ready.names_1 Like temp_query.last_name & " *" 
  or
  print_ready.names_1 Like  "* " & temp_query.last_name & " *"
  )
  OR
  (
  print_ready.names_2 Like "*," & temp_query.first_name &  " " & temp_query.middle_initial & " *"
  or
  print_ready.names_2 Like "*," & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_2 Like "* " & temp_query.first_name & " " & temp_query.middle_initial
  )
  and 
  (
  print_ready.names_2 Like temp_query.last_name & ",*" 
  or
  print_ready.names_2 Like temp_query.last_name & " *" 
  or
  print_ready.names_2 Like  "* " & temp_query.last_name & " *"
  )
  ),
  (
  (
  print_ready.names_1 Like "*," & temp_query.first_name & " *"
  or
  print_ready.names_1 Like "*," & temp_query.first_name 
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " *"
  )
  and 
  (
  print_ready.names_1 Like temp_query.last_name & ",*" 
  or
  print_ready.names_1 Like temp_query.last_name & " *" 
  or
  print_ready.names_1 Like  "* " & temp_query.last_name & " *"
  )
  OR
  (
  print_ready.names_2 Like "*," & temp_query.first_name & " *"
  or
  print_ready.names_2 Like "*," & temp_query.first_name 
  or
  print_ready.names_2 Like "* " & temp_query.first_name & " *"
  )
  and 
  (
  print_ready.names_2 Like temp_query.last_name & ",*" 
  or
  print_ready.names_2 Like temp_query.last_name & " *" 
  or
  print_ready.names_2 Like  "* " & temp_query.last_name & " *"
  )
  )
  )
  );

I can give a couple specific examples I noticed right away. It should be returning 80 records, but it's returning 66, which means the query fails for some records. Below are records that should have been returned but weren't:

This is how they look in temp_query:
last_name || first_name || middle_initial
Blair || Sheron || S
Brown || Wanda || R
Rodriguez || Lillian || M
Glaubman || Alan ||

I added the || here just to indicate a column break

This is how they appear in print_ready:
names_1
BLAIR,SHERON S
BROWN,BRENON I H/E BROWN,WANDA R
RODRIGUEZ,LILLIAN M
GLAUBMAN,ALAN & SHORSTEIN,LILLIAN


Thanks for response.