Partial String Match - Two Tables

[mwilliams]

Hi all,

I have a problem i hope someone can help me with

I have two tables both created by importing data from an external source, the first table is called [Ext_Master] and has a field called [TrackingCode].
The second table is called [CodeLookup] and contains a field called [CodePrefix].

I would like to pull back data from the second table [CodeLookup] where there is a partial match between the two fields [TrackingCode] and [CodePrefix].

The problem lies with the formatting of the data, the [CodePrefix] does not appear in the same location or format within [Tracking Code] for example:

[TrackingCode]
KNC-GCH-FCH-GEN-PFB
i-a82d-00001000
e-2072-00001000

[Code_Prefix]
KNC
a82d
2072

Thank you for any help

mark.

 

[John Big Booty]

Welcome to the forum.

You can use the following in your criteria;

Like "*" & [CodeLookup].[Code_Prefix] & "*"

 

[mwilliams]

Hi John and thanks for the welcome

I have tried the code you supplied however i dont get consistent matches.

for example:

[TrackingCode]
KNC-GCH-FCH-GEN-SUP-EMC
KNC-GCH-FCH-GEN-SUP-EMC
KNC-GCH-FCH-GEN-SUP-EMC
i-a82d-00001000
i-a82d-00001000
i-a82d-00001000

[Code_Prefix]
KNC

KNC

0
a82d


As you can see there are couple of blanks, and one value of 0 being returned?

The data in [TrackingCode] appears to be identical on those with inconsistent matches.

thanks

 

[John Big Booty]

Are you able to post a representative sample of the data?

 

[mwilliams]

Please see the attached, its just an excel file with the two fields in question.

The [Tracking_Code] is a just a small sample of the 800,000 records, however the [Code_Prefix] is the full list.

thanks

 

[John Big Booty]

You need to remove the blank records. Have a look at the attached.

 

[mwilliams]

Hi John,

Thanks for the example database, I can now see where its worknig correctly and where it has issues. The code you supplied works perfectly in 95% of the records, however there are some special cases that i think may need hard coding

for example ref_1234_Wireless would fall under the 'Wireless' code prefix and not 'Ref'.

Its a bit of a pain but i cant see any way around it.

Thanks again for all your help.