| Author |
Topic |
|
kidroot
Starting Member
4 Posts |
 Posted - 2009-01-06 : 12:59:58
|
| I am having great difficulty creating a query to return records from a table(s) where any value in the last name field in the employee table exists WITHIN ANY value within a field (full name) that contains peoples' full names in a different table. For example, my PERS2 table has a value in the last name called SMITH, but my other table would have the data as BLACKSMITH CONSTRUCTION COMPANY.SELECT *FROM PERS2, CONTRAWHERE PERS2.LASTNAME In (CONTRA.FULLNAME);Any assistance would be much appreciated! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-01-06 : 13:01:59
|
| Use the LIKE() function.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
 |
|
|
kidroot
Starting Member
4 Posts |
Posted - 2009-01-06 : 13:18:55
|
| Thanks. I tried LIKE instead of IN but the query returned 0 records just like the IN query. From another query I previously ran, I know I have at least 61 instances where the last name exists somewhere in the full name field. I cannot figure out why the IN and/or LIKE queries will not return these 61 records at a minimum.Any thoughts? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kidroot
Starting Member
4 Posts |
Posted - 2009-01-06 : 14:16:47
|
| THANKS! I was able to get a similar one to work by slightly adjusting the one you posted.SELECT *FROM PERS2, CONTRAWHERE PERS2.LASTNAME Like '*'+CONTRA.FULLNAME+'*';THANKS AGAIN!I did notice though that I am unable to get this to work in a similar query that has Russian instead of English as the text. Any thoughts as to what would cause this? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-01-06 : 14:51:08
|
| What is the datatype and unicode compression setting of the column with the Russkie data?"When a field's Unicode Compression property is set to Yes, any character whose first byte is 0 is compressed when it is stored and uncompressed when it is retrieved. Because the first byte of a Latin character— a character of a Western European language such as English, Spanish, or German— is 0, Unicode character representation does not affect how much storage space is required for compressed data that consists entirely of Latin characters."You may need to convert your comparison string to unicode or something to get this to work.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
kidroot
Starting Member
4 Posts |
Posted - 2009-01-06 : 17:17:51
|
| Thank you! those are very helpful suggestions. The fields are "Text" and do not have Unicode compression. I copied the tables again and changed the Unicode compression and it did not affect the output of the query. This is very strange because other more "basic" queries appear to be executing properly against this non-English data.I would love to hear any other suggestions if anyone has them. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-01-07 : 09:49:03
|
| Try posting your question on an MS Access forum: http://www.dbforums.com/microsoft-access/________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
sql query to find name in a field