How to display data from 1 main table and 4 supporting tables

[millerk]

I've got 1 primary table and 4 other tables. Each of these other tables has a many-to-one relationship to my main table.
For example:
Person(SSN)
Alias(SSN, FirstName, LastName)
Address(SSN, Street, City, State)
Phone(SSN, PhoneNum)

Each person can have many aliases, many addresses, and many phone numbers. I already have a query that is joining all this data and placing it in one flat, denormalized table(I'm doing this to log search results). Something like this:
SSN FName LName City
---------------------------------
111-11-1111 James Smith Chicago
111-11-1111 James Smith New York
111-11-1111 Jimmy Smith Chicago
111-11-1111 Jimmy Smith New York
555-55-5555 William Jonston Chicago
555-55-5555 William Jonston Seattle
555-55-5555 William Jonston San Francisco
555-55-5555 Bill Johnston Chicago
555-55-5555 Bill Johnston Seattle
555-55-5555 Bill Johnston New York

Unfortunately, that's a lot of redundant data, so is there a way in Crystal to group or otherwise filter that data so it looks more like this:

111-11-1111 James Smith Chicago
Jimmy Smith New York
555-55-5555 William Jonston Chicago
Bill Johnston Seattle
New York

or maybe something like:

111-11-1111
James Smith, Jimmy Smith
Chicago, New York
555-55-5555
William Jonston, Bill Johnston
Chicago, Seattle, New York

Thanks

 

[millerk]

Or if I need to use all the separate tables as a data source, can you point me to an example or walk me through doing it that way?

 

[lbass]

You could insert a group on SSN, and then create formulas like this:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar name := "";
stringvar city := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar name;
stringvar city;

if instr(name,{table.first}+" "+{table.lastname}) = 0 then
name := name+ {table.first}+" "+{table.lastname} + ", ";
if instr(city,{table.city}) = 0 then
city := city + {table.city} + ", ";

//{@displayname} to be placed in group footer_a:
whileprintingrecords;
stringvar name;
left(name,len(name)-2);

//{@displaycity} to be placed in the group footer_b:
whileprintingrecords;
stringvar city;
left(city,len(city)-2);

-LB