Grouping by Lastname 2

[smil3y]

I thought I was doing so well. Created all these reports - sorted etc and to give the reader what they wanted I grouped on last name. Used a formula to join the lastname and firstname together. Looked great.
Then I noticed that although the database held three lastname fields with Jones, only one ever showed in the report.
Has anyone any ideas on how to group by lastname but present all the last names and first names.
ie Three names in the database Fred Jones, Bob Jones and Sam Jones. The SQL query obtains all records and sorts them in ascending lastname order.
When crystal reports does its grouping all records for all the jones get listed under Bob Jones and the other 2 do not rate a mention.

 

[synapsevampire]

A grouping is intended to GROUP like records... Jones is alike.

What you want is a formula which groups on Lastname + firstname + some uniqueid

Then just display whatever you want, you don't have to display what you group by.

The reason why you add in the unique ID is that you might get numerous John Smith's, so you'd fall back to just one grouping by just having the last+first name, and I assume that you want them in alpha order.

-k

 

[smil3y]

I agree that this looks like the way to go. I do have a unique ID and lastname and firstname fields. Can you give me some idea how to put this type of formula together. Once the formula is created do I simply group by this formula.

 

[Turkbear]

Hi,
If you have a lastname field then use that to group and place your concatenated full name formula in its details, it should show you all the Jones like
GH Jones
D Fred Jones
Bob Jones
Sam Jones
GF

To further sort the full names, sort by the lastname,firstname

Here's an example from our data:

Pearson
 Cary L Pearson
 David D Pearson
 David K Pearson
 Glen W Pearson
 Kenneth E Pearson
 Kenneth R Pearson
 Sara Colleen Pearson

You can ,of course, supress the GH and GF to hide the lastname field so the 'sort' is invisible.

Hope it helps..

 

[synapsevampire]

How you construct the formula for grouping is dependent upon the type of ID field, but it would look something like:

{table.last}+{table.first}+totext({table.id},0,"&quot

You'll end up with as many groups as detail rows.

-k