Join records of each group

[thg]

Hello, thanks in advance for your help / comments

I have 2 tables:

SampleInfo contains 2 columns: SampleID & SampleName
Analysis contains 2 columns: SampleID & Elements

I link these 2 tables, get the SampleName & Elements out by the code:

SELECT SampleInfo.SampleName, Analysis.Elements

FROM SampleInfo INNER JOIN Analysis ON (SampleInfo.SampleID = Analysis.SampleID)

It would display

SampleName | Elements
A | a
A | b
A | c
A | f
B | a
B | g
B | l
C | c
C | s
C | o
C | m
C | n

I need to display the report as following:

SampleName | Elements
A | a, b, c, f
B | a, g, l
C | c, s, o, m, n

QUESTION: is it possible? If it is, how should I do this?

FYI, I use CR10 & SQLServer 2000 database

Regards,
t

[vidubhushan]

Hi,

I found some solution to yor post.

I created a report with excel as datasource.

Grouped the report on Sample Name field.
Created two formula fields to get the results.
1. Elements -- Formula Field
Code for formula filed as follows :
whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;
2.ResetElementValue -- Formula Field
whileprintingrecords;
shared stringvar Elements;
Elements:="";
-- Place the @Elements fromula filed in detail section and suppress the section.
-- Place the Group Name in Group Footer section
-- Place @Elements in the same group Footer section
-- Place the ResetElementValue formula filed in the Group Header section and suppres

Try with the following format and let me know will it fulfills your requirement.

Thanks,
Vidu.
-- Group

[thg]

Vidu,
Thanks for your help. It was a great start. I only had to change the Elements (formula field) a little as below. If I don't have the ELSE IF, it will double the last element in each group.

whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else IF Elements <> Right(Elements, length({Sheet1_.Elements}) then
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;

Again, your help is truly appreciated.

[Madhi]

or place the formula in group footer and suppress the details and group header