I'm trying to tie to separate databases together based on a location. However, each DB handles locations differently. I'll explain the fields:
DB1 - The address has only three fields for a location:
digits Street_Name Apartment
123 Main St #5
456 S Main St
123 Jack Spree Rd
123 Calle Oriente
DB2 - The address has five fields for a location:
dig Dir Street Type Apt
123 Main St 5
456 S Main St
123 Jack Spree Rd
123 Calle Oriente
Anyway, I have a Crystal Report that will check DB1. What I'm trying to do is click on the street name that will open up a sub report, parse out the street name correctly, and query DB2 to get the data for the same exact address. As seen from the above examples of the databases, The digits and apartment fields are not a factor. The problem is that the street name in DB one has to be broken down into three different fields to query DB2. The four examples that I give shows the uniques problems to most of translation problems that could occur. I might have more, but I can cross that bridge later.
Any ideas about the formula I should use? I did have one thought on the Direction. If DB1.Street_name starts with N, S, W, or E and has a space after it, dump that into DB2.Dir, then start with 3rd character to begin street name. Now, parsing out the street name with multiple variables that pop up is where I'm losing it.
Thanks
DB1 - The address has only three fields for a location:
digits Street_Name Apartment
123 Main St #5
456 S Main St
123 Jack Spree Rd
123 Calle Oriente
DB2 - The address has five fields for a location:
dig Dir Street Type Apt
123 Main St 5
456 S Main St
123 Jack Spree Rd
123 Calle Oriente
Anyway, I have a Crystal Report that will check DB1. What I'm trying to do is click on the street name that will open up a sub report, parse out the street name correctly, and query DB2 to get the data for the same exact address. As seen from the above examples of the databases, The digits and apartment fields are not a factor. The problem is that the street name in DB one has to be broken down into three different fields to query DB2. The four examples that I give shows the uniques problems to most of translation problems that could occur. I might have more, but I can cross that bridge later.
Any ideas about the formula I should use? I did have one thought on the Direction. If DB1.Street_name starts with N, S, W, or E and has a space after it, dump that into DB2.Dir, then start with 3rd character to begin street name. Now, parsing out the street name with multiple variables that pop up is where I'm losing it.
Thanks