join - Crystal Reports & Raiser's Edge: Selecting Data from 2 Joined Tables -

i have strange issue. have data stored in 2 tables.

table 1: contains data individual

• individualid
• birthdate
• email

table 2: contains individual names split 3 fields each having key.

• individualid
• nametype
• name

this means nametype = 1, surname, nametype = 2, middle name, , nametype = 3 firstname.

i need create crystal report list individual's name in 1 line, such as

surname + firstname + middlename

the problem experience related how pull data out , joins.

if create join between these 2 tables left outer join, cannot select nametype select. means tht surname, other times middlename, etc.. seems tho join not consistent. furthermore, cannot iterate on expected 3 values seems can pull same 1 every time.

i have tried fix adding command in turn select each of names second table. however, report meant integrated called raiser's edge not support command objects. in other words, command objects not option me.

so options here? prefer not using subreport know work, has performance overhead. there other way this?

thanks

the easiest way group on individualid , keep track of first, middle, , last names via variables. there's no need worry record selection or joins, really. try this:

//formula group header whileprintingrecords; stringvar first := ""; stringvar middle := ""; stringvar last := "";  //formula details section whileprintingrecords; stringvar first; stringvar middle; stringvar last; select {table2.nametype} case 1 : first := {table2.name} case 2 : middle := {table2.name} case 3 : last := {table2.name}  //formula group footer (the section displayed) whileprintingrecords; stringvar first; stringvar middle; stringvar last;  last + ", " + first + " " + middle