Creating field to join other tables to

[AcctSolver]

I need to join on table to various others, but need to create the field to do so.
Table P contains fields: RunNo, sequence, weight. I need to build the field RunNum, and use it for joins.
There is always a sequence 1 for each RunNo, but there can be a sequence 2 or 3 as well.
If there is a sequence 2 or 3, then the sequence is converted to a letter; 1=A, 2=B, 3=C. In that case, RunNo + converted sequence = RunNum.
Else, if there is no sequence besides 1, RunNo = RunNum.
What syntax would I need to use to build that RunNum field? All the other tables already have RunNum in the other format, but I need the data from table P for my report.

 

[SkipVought]

Hi,

Please post a cogent example.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[AcctSolver]

I have RunNo, Sequence and Weight as below, and need to produce RunNum per the rules above
RunNo Sequence Weight RunNum
AZ15123 1 42.5 AZ15123A
AZ15123 2 41.7 AZ15123B
AZ15124 1 82.0 AZ15124
AZ15125 1 81.3 AZ15125
AZ15126 1 25.6 AZ15126A
AZ15126 2 54.3 AZ15126B

 

[CoSpringsGuy]

try this or a variation of. Create an SQL command table like this

Select a.RunNo, a.Sequence, a.Weight, a.RunNo||char(a.Sequence+64) as RunNum2
from tableP a

Link your other tables on RunNo and RunNum2

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection