Join Tables by almost simliar fileds

[delta010]

Hi,
I'm trying to join 2 tables together. I need to match a single field together. In Table1 I have a column called Source that has data like AAAAAA_AAAA_123_AA_1. In Table2 I have a column called Source that has data like 123_AAA_1 which is Table1.Source minus the first 12 characters.
I have tried to do a Left(Table1.Source, 12) but I'm not sure exactly how to write the query. I'm actually trying to use another field from Table2 in a report with Table1 but I think I have to link them based on similar data.
Any help would be greatly appreciated.
Thanks.

 

[synapsevampire]

I would suggest that you do this on the database side (SP, View), I fear linking these in CR will require subreports, and so it will be VERY inefficient and high maintenance.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[HowardHammerman]

k is right.

You COULD do it with sub-reports by creating a formula field in a report that uses Table 1, subtracting the first 12 digits from the source field and using that to join to table 2.

High maintenance. Howard Hammerman,
Crystal Reports training, consulting, books, training material,support. HammerLauch program runs reports without Crystal
howard@hammerman.com

http://www.hammerman.com

800-783-2269