×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Crystal SQL Code linking to partial database field entries

Crystal SQL Code linking to partial database field entries

Crystal SQL Code linking to partial database field entries

(OP)
Being in the Travel Industry I am always dealing with fields and tables in a database associated with flight bookings. I have a issue that has stumped me for a long time now and was looking for some guidance.

In one table (BOOKING) there is a field that contains the flight routing in linear "code" fashion like ... SEADENATLMIA, which stands for Seattle - Denver - Atlanta - Miami
In another separate table (CITY) there is a listing of all worldwide City Codes in one field and the corresponding City Name in another.
SEA, Seattle
DEN, Denver
etc

For Crystal Reports ... I need to extract the linear SEADENATLMIA info from the BOOKING table and show "Seattle Denver Atlanta Miami" as one field on a Crystal report (space delimiter)

I can achieve my end goal by doing this in 2 steps in MS Access but I really would like to do this on the fly in one step in Crystal (if possible)

In MS Access
1. Create a query to extract the individual 3 letter city codes from SEADENATLMIA ... by using the LEFT and MID functions
field 1 CityCode1: LEFT([BOOKING].[ROUTING],3)
field 2 CityCode2: MID ([BOOKING].[ROUTING],4,3)
field 3 CityCode3: MID ([BOOKING].[ROUTING],7,3)
etc

2. I create a second query based on the first query from above ... link the above 3 letter [CityCode1-3] fields individually to the [CITY] table's 3 letter code to get the City Name in the form of ... CityName1+" "+[CityName2}+" "+[CityName3] etc

Is it possible in Crystal to do some sort of linkage for each 3 letter city code found in the linear field in SQL and link them to the City table containing the City Names and subsequently giving me ... Seattle Denver Atlanta Miami?

Hopefully I am explaining myself clearly.

Any assistance would be greatly appreciated

Gary


RE: Crystal SQL Code linking to partial database field entries

You should only post in one Crystal Reports forum. I responded in the Formulas forum.

-LB

RE: Crystal SQL Code linking to partial database field entries

(OP)
Sorry .. me bad!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close