sub-report in Group header duplicates Detail rows 1

[Tracy62]

Hi (apologies if this message appears twice - it disappeared half way thru typing so I'm trying again)

I have unique field information in my Group Header (Member.ID, Member.Name, etc) and then the Details list all Hospitals and Units earned. The Group footer has a summary of units earned.

My problem is that I want to display the Trainers associated with the Member in the Group header. But there can be more than one trainer (mostly 2 per Member - and the trainers are stored partly in a separate table, with their names in the same table as the members). Putting that field in the Group header only displays the first or max Trainer. I then tried inserting the trainers as a sub-report in the group header, but that created duplicate rows in the detail section.

How do I display multiple trainers with the Member's unique information, but don't duplicate Details?

(here is what I currently get:
Group ONE:
Member: John Smith
ID: 3456
Trainers: Joe Blow, Ann Jones
Details:
Hospital 1 5 units
Hospital 1 5 units
Hospital 2 3 units
Hospital 2 3 units
Group ONE footer:
Total Units 16 units

and here is what I want:
Group ONE:
Member: John Smith
ID: 3456
Trainers: Joe Blow, Ann Jones
Details:
Hospital 1 5 units
Hospital 2 3 units
Group ONE footer:
Total Units 8 units )

 

[MisterMo]

you could create a further group for Hospitals and place the details there, then suppress the detail section

then instead of summary use the running totals option and on the evaluate section choose the on change group and select Hospitals

Mo

 

[Tracy62]

Thanks so much for responding, MisterMo. Unfortunately your solution doesn't work. I've tried what you suggested just now and previously tried every which way using new groups. The problem is that I have to show all the details. When I add in the Trainers in a Group header, they can have multiple values and therefore multiply the groups and details below them. This is because the ID code for a Trainer is a not unique. (NB it wasn't ME that designed this database!).

The only way I can think to do this is to go to the data source and somehow either link tables, or create a view, that doesn't bring up multiple rows for trainers associated with a Member (so that I get a one-to-one correspondence). I have an idea where if we force only one unique code per Trainer, then have the users always enter second trainers with the same code but ending in something like "-2nd" then I can do a self-join to get trainers listing across a row, delete rows with code like "-2nd" (because they appear as the second in other rows) it all might work. Does this sound crazy? (I'll try it when I get 5 minutes to work on it).

Have you had a problem like this before?

 

[MisterMo]

could you give a sample of your actual data?

Mo

 

[Tracy62]

It's ugly....

Table 1: Training Info
MemberID Hospital Units_earned
32 RMH 4
32 Vincents 3
33 Vincents 6
33 RMH 2

Table 2: Name
MemberID Full_Name etc...
32 John Smith
33 Susie Brown
34 Joe Blow [this is a trainer]
35 Ann Jones [this is a trainer]

and the trainers are associated with the Hospitals thru a third table (no unique key)

Table 3: Trainer
MemberID Hospital
34 RMH
35 RMH
34 Vincents
35 Vincents

So Table 1 is joined to Table 2 to get the names of trainees, and Table 3 to get the trainers. Table 3 is joined to another instance of table 2 to get the trainer's names.

Tracy

 

[lbass]

How do you know that Joe Blow and Ann Jones are trainers? Is there another field in the Name table that distinguishes members from trainers, or is the only way you can tell by the presence of the memberID in the trainer table?

-LB

 

[Tracy62]

The only way you can tell is by the presence of the memberID in the trainer table (I repeat: it wasn't me that designed this db)

 

[MisterMo]

Then simply add the Names Table twice

SELECT     N1.MemberID, N1.FullName, TI.Hospital, TI.UnitsEarned, T.MemberID AS Expr1, N2.FullName AS Expr2
FROM         dbo.[Names] N1 INNER JOIN
                      dbo.TrainingInfo TI ON N1.MemberID = TI.MemberID INNER JOIN
                      dbo.Trainer T ON TI.Hospital = T.Hospital INNER JOIN
                      dbo.[Names] N2 ON T.MemberID = N2.MemberID

this is a sample query based on the data you posted.

this will bring duplicates which can be removed by grouping the data from Crystal

Mo

 

[lbass]

You could try creating a formula like {@Trainer}:

if notisnull({trainer_1.memberID}) then {Name.FullName}

Then create a second formula:

stringvar x;

x :=
nthlargest(1,{@trainer},{Name.Member ID})+", "+
(if distinctcount({@trainer},{Name.Member ID}) > 1 then
nthlargest(2,{@trainer},{Name.Member ID})+", ")+
(if distinctcount({@trainer},{Name.Member ID}) > 2 then
nthlargest(3,{@trainer},{Name.Member ID}));
left(x,len(x)-2)

Place this in the group header for Member ID. Not sure whether you would get dupes though. If so, you could use the logic of the first formula in a subreport for the group header.

-LB