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
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
