Use like statement with a join?

[roc100]

Hi all,
I am trying to do a left join comparing two descriptions. It works great however there are several fields that are only a parital match. I have tried using a LIKE statement in the Where field, but it will not return the results I need. I am using the join to compare two Desciption fields. I then put specific criteria in the were field so when the description in table 2 is "Like "EIM *" it will match to table 1 and return the bank label from table 2. This does not work. I can not use partial join queries such as the one shown here because it is not specific enough and matches wrong values.

I can not use Left( ), 3) because there is a different number of values in the descriptions and over 100 of them. I believe it needs to be a like statement to return the correct results. In most cases the first left part of the description in table 1 does match to table 2, but again the amount of values vary for each description. They are all very similar but need very specific labels.

Table 2 DESC TABLE 2 BANK LABEL Table 1 DESC
EIM MC EIM EIM 12897
CMA MC CMA CMA 12
EIM DISC MC EIM DISC EIM DISC 45673

I am using a left join to compare these two fields and return all data from table 1. If TABLE 1 DESC and TABLE 2 DESC match, I need the bank label IN TABLE 2 returned.

This is what I have that does not work:
SELECT [TABLE 1].[DESC], [TBL 2].DESC
FROM [TABLE 1] LEFT JOIN [TBL 2] ON [TABLE 1].[Desc] = [TBL 2].DESC
WHERE ((([TABLE 1].[Desc]) Like "EIM *"));

Any help is greatly appreciated!

 

[plog]

I iterate my advice from when you first posted this (http://www.access-programmers.co.uk/forums/showthread.php?t=216922): Create a mapping table.

 

[roc100]

Thanks for your help. The problem is that one of the sets of data that I am comparing changes every time I run the query (daily). For example:

CMA1234
the next day I run the query will be
CMA1111

and there are about 100 different values like this like OMG1234 and PIN1234.

So creating a mapping table is essentially what I am doing already by manually matching them and filling in the necessary data.

Any ideas or suggestions? Thanks!

 

[plog]

Try and write out a comprehensive set of linking rules in plain english first. You've said that sometimes the matching fields exactly match, but other times they don't. And when they don't you can't use the LEFT function because that causes too many matches. So what do you do?

You might have to manually make matches a couple times until you get a handle on your matching rules. I would go ahead and make a matching table like I suggested and after your done look at all the matching fields that aren't identical to see exactly how you determined their matches. That will lend some insight into a process to do it automatically in the future.

 

[roc100]

I will give this a try. Thanks again for all of your help -

 

[roc100]

Hi again,
I understand the concept of the matching table which normally would work. However the problem is that I will have to put the new data that changes every day into the matching table and tell it what to match to.

So "CMA 12345" matches with "CMA". The next day "CMA 11111" will need to match with CMA. Everyday I will need to go into the matching table and put the new data, in this case "CMA 11111", and tell it to match with CMA. My goal is to set it up so that "CMA 12345" will match with "CMA" on Monday, and the next day when I pull in the new data "CMA 11111" will match with "CMA". Thanks again for your willingness to help- your expertise is greatly appreciated!

 

[jdraw]

I think you need to rethink exactly what you are trying to do. If you are attempting to match strings on a daily basis and those strings change every day, then it's time to restructure what you are trying to do. At best you are just guessing - as i see it.
You need to abstract whatever the data is or means and get it into some sort of useful structure where comparisons are possible.

Just my $.02

 

[roc100]

Is there a way to pull out the information I know will be there using an exact match?

I know that there will be "CMA" in the value.
CMA 12345
CMA 11111
CMA 22222

Is there a way to pull out the "CMA"? I could then match them up to the other table. I would need to do this for multiple values.

OLM 12345 pull out the "OLM"
CMA 12345 pull out "CMA
BC HOME 12345 pull out "BC HOME"
CARD 12345 pull out "card"
BC Card 12345 pull out "BC Card"
and it goes on for over 100 values.

Thanks.

 

[plog]

Now your getting it. What do all of those have in common?--The matching part is all of the characters prior to the appearance of a number.

Below is a function that will take your string apply those rules to it and return the matching value.

Function getLinkValue(s) As String
    ' takes string and returns substring before first numeric value in string
maxi = Len(s)           ' length of input string, controls looping through characters
NumberPos = maxi        ' position of first number in string
For i = 1 To maxi
    ' loops through every character to see if it is a number
    ' if first number in string, assigns that to NumberPos
    If IsNumeric(Mid(s, i, 1)) And i < NumberPos Then NumberPos = i
    Next
If NumberPos < maxi Then NumberPos = NumberPos - 2
    ' if number was found backtracks NumberPos to last character in string
 
getLinkValue = Mid(s, 1, NumberPos)
 
End Function

Now, go back to your data and see if you can find any examples of exceptions or where that rule is incorrect.

 

[roc100]

Thank you so very much this works great! There are some exceptions (of course!) Is there anyway around them? My main concern is distinguishing the ones with "DESC" after a number. Thank you!!

AC5 DISCAFM3 2057557FFM4 2057691FFM5 2062239FFM5 DISC 2061005FFM6 2062999FFM6 DISC 2059269FFM7 2062967FFM7 DISC 2061035FFM8 2062987FPC 835 1228265PCPC835 1956282P

 

[plog]

Your text is all jumbled together. Could you display that data again along with what the match data for each value should be?

 

[jdraw]

Jumbled is the word.

Can you take 1 or 2 records and tell us what each represents?\

For example
AC5 DISCAFM3 2057557FFM4 2057691FFM5 2062239FFM5 DISC 2061005FFM6 2062999FFM6 DISC 2059269FFM7 2062967FFM7 DISC 2061035FFM8 2062987FPC 835 1228265PCPC835 1956282P

Do these blocks of characters have some meaning?

Also,

distinguishing the ones with "DESC" after a number

should that be DESC or DISC

 

[vbaInet]

I'm not fully understanding your requirement but how about:

SELECT DISTINCT [TableToMatch].*
FROM [TableToMatch] INNER JOIN 
    (
     SELECT P.[Desc] 
     FROM [TableWithPattern] AS P
    ) AS Q 
    ON [TableToMatch].[Desc] LIKE Q.[DESC] & '*';

And if you are performing a row by row comparison between two tables, i.e. row 1 DESC against row 1 DESC, what did you say was wrong with InStr()?

 

[roc100]

Sorry about that. Many thanks to you all for taking the time to help me out.

"FM3 2057557" needs to match with "FM3".
"FM4 2057554" needs to match with "FM4"
"FM7 DISC 2061034" needs to match with "FM7 DISC"

There are a few of these. When I use the function to pull all text prior to the numbers it pulls "F".

The problem with InSTR is that many of the values I am matching have similar words and it was matching to incorrect fields and returning duplicate values. I can't do Left INSTR(field,3) because the number of values I am matching to in the field varies. For example:

CARDIO DISC 123546 needs to match with "Cardio DISC"
and
CARDIO 13265413 needs to match with "Cardio"

The function plog gave me seems to do the trick other than the few exceptions.

 

[vbaInet]

So what is the ID that uniquely identifies this record:

"FM3 2057557" needs to match with "FM3"

?? ... for example.

 

[plog]

So, in plain english, the match rule is now: The matching part of the string is all of the characters before the last space that appears in the string.

Using that definition I came up with this code which should work for all cases we have found:

Function getLinkValue(s)
    ' takes string and returns substring before last space character in string
maxi = Len(s)           ' length of input string, controls looping through characters
LastSpace = maxi        ' position of last space character in string
For i = 1 To maxi
    ' loops through every character to find position of last space
    If (Mid(s, i, 1) = " ") Then LastSpace = i
    Next
If LastSpace < maxi Then LastSpace = LastSpace - 1
    ' if Space was found backtracks LastSpace to last character in string
 
getLinkValue = Mid(s, 1, LastSpace)
 
End Function

If you think the plain english definition is wrong or you find any other cases let me know. When doing something like this always start with simple language to define what you are trying to do rather than just attacking it with code and queries--it will help you boil down the problem and get your mind around it.

 

[roc100]

Unfortunately this eliminated other data I need such as "AIR DISC". I need the DISC part to be left in there. I am going to go with the first function you gave me and see if I can eliminate some of the exceptions at the time of data entry.
I do wish there was a way to create a table with two columns that says when you see this exact phrase plug the data next to it in and ignore all other data but it is just not that simple.

Thank you so much PLOG for all of your help! It has helped me to change my way of thinking and will certainly help me in the future.