Like Query for similar text (1 Viewer)

Hi all,

Hopefully someone is able to help me, as I have a fairly limited Access knowledge

What I am trying to do, is take a series of text strings which indicate the grade variant of a specific car make and model (e.g. SPORTBACK TFSI S LINE, TDI S, TDI SE) and then assign a level to each variant.

I have used the below query to do this by saying where Marque = X, Range = Y and Variant Name is like the variant name given (Using *) (I.E. Pulls out 'S LINE' from 'SPORTBACK TFSI S LINE'


LEFT JOIN tbl_Gradelevel ON (tbl_MASTERGRADE.Variant_Name Like '*' & tbl_Gradelevel.Grade& '*') AND (tbl_MASTERGRADE.Range = tbl_Gradelevel.Model) AND (tbl_MASTERGRADE.Marque = tbl_Gradelevel.Brand);



This is successful in working where the grades are clearly different, E.G. Ultimate, Tekna etc.

However where the grades are very similar it gets confused as I assume by using Wildcards *S* is the same as *SE*

Does anyone have a fairly simple solution that would help to differentiate the names which are confusing my query

Many thanks

However where the grades are very similar it gets confused as I assume by using Wildcards *S* is the same as *SE*

Click to expand...

No, bad assumption.
*S* ==> means an "S" anywhere in the string
*SE*==>means "SE" anywhere in the string

Please post some examples and specific descriptions of what you are using and trying to accomplish.

See this for examples of wildcards in MS Access.

Hi. I don't understand you question, but I think you assumed wrong. *S* is not the same as *SE*. The first one will return SEDAN but the second one will not return SPORT. So maybe you need to use a minimum number of characters to match.

Hi Both,

Thanks for your replies

So, My Raw data comes in the below format

Marque Range Variant Name Grade Level
VW Golf S TDI
VW Golf Blue Motion SE Naviagation
VW Golf SE NAVIGATION TDI


I then have a separate table

Marque Range Variant Grade Level
VW Golf S High
VW Golf SE Low

What I am trying to do is say, where marque is VW and Range is Golf, if the variant name is "S" (Which I have to pull out of the text string) update grade level to High. And where Variant name is "SE" update Grade level to "Low". But of course it cannot do this as *S* is the same thing as *SE* and so all i get for those with a variant name of "SE" is alternating results of High, Low

Hope this makes more sense

Thanks

can you show some Grade?

mybe something like this:

tbl_MASTERGRADE.Variant_Name & " " Like "*" & tbl_Gradelevel.Grade & " *"

When you test a string, test for the most unique (longest)string first.
If, as your example, you test for "*S*", you'll get strings containing S or SE.
Check for *SE* first, update your other field.
Then check for *S*, where that other field has not been populated.
Hope it helps.

Redberry95 said:

Hi Both,

Thanks for your replies

So, My Raw data comes in the below format

Marque Range Variant Name Grade Level
VW Golf S TDI
VW Golf Blue Motion SE Naviagation
VW Golf SE NAVIGATION TDI


I then have a separate table

Marque Range Variant Grade Level
VW Golf S High
VW Golf SE Low

What I am trying to do is say, where marque is VW and Range is Golf, if the variant name is "S" (Which I have to pull out of the text string) update grade level to High. And where Variant name is "SE" update Grade level to "Low". But of course it cannot do this as *S* is the same thing as *SE* and so all i get for those with a variant name of "SE" is alternating results of High, Low

Hope this makes more sense

Thanks

Click to expand...

Hi. In your sample data, it appears either S or SE are separate words. In that case, you'll get a better match if you use Like "* S *" and Like "* SE *". In other words, include a space character in your search pattern.