Combining detail lines in a report

[jgarnick]

I have a report that has several detail lines per person. I would like to be able to combine the field from each of the person's records into one field on the report, or at least on the same line.<br>
<br>
For example:<br>
<br>
Instead of:<br>
<br>
DETAIL: Joe Smith 1990<br>
1992<br>
1993<br>
1996<br>
Mary Jones 1980<br>
1987<br>
1989<br>
<br>
I would like to have:<br>
<br>
DETAIL: Joe Smith 1990,1992,1993,1996<br>
Mary Jones 1980,1987,1989<br>
<br>
The other way takes up too much paper!<br>
<br>
Any ideas?

 

[JimHorton]

Look into the MoveLayout property. I recall doing exactly what you want a few years ago. I think you set the MoveLayout to false on the detail format, then move the control.left property. Something like (in the OnFormat Event)<br>
'assuming a control is called FirstName<br>
Me.MoveLayout = False<br>
If Me!FirstName.Left &gt;= (max Right limit you decide) Then 'If we hit the right edge<br>
Me.MoveLayout = True<br>
Me!FirstName.Left = 0 '(or reset to whatever leftmost edge you have)<br>
Else<br>
Me!FirstName.Left = Me!FirstName.Left + Me!FirstName.Width 'arbitrary<br>
End If<br>
<br>
Since you'll have more than one control, I'll leave the math up to you to calculate where to place the x,y of each control as you move it and reset it back to the left edge when needed.<br>
--Jim

 

[elizabeth]

If the values are all int he same range you might like it as a crosstab.

 

[JimConrad]

You could also do something similar to &quot;newspaper columns&quot;. Setting this up is done via the &quot;Page Setup&quot; dialog.<br>
<br>
Get to page setup from the Access Menu bar... Select Edit/Page Setup. Then select the &quot;Layout&quot; tab.<br>
<br>
Need more help? I'll keep an eye open for another post from you on this thread. Or you can email me. <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[rochelle]

I would create a function that would string together the values you want and embed this function in the query that is your report recordsource. This recordsource should provide only one detail record per person.<br>
<br>
On the report, show the calculated field from the above query on each detail line. Set can grow = Yes for both the detail section and for the calculated field's text box.<br>
<br>
Public Function StrungOut(DetailID As String)<br>
Dim strSQL As String, dbs As Database, rst As Recordset, tmp As String<br>
strSQL = &quot;SELECT Detail.[YearID] FROM [Details] WHERE ([Details].[DetailID])='&quot; & DetailID & &quot;'));&quot;<br>
Set dbs = CurrentDb<br>
Set rst = dbs.OpenRecordset(strSQL)<br>
rst.MoveFirst<br>
While Not rst.EOF<br>
If Len(tmp) &gt; 0 Then tmp = tmp & &quot;, &quot;<br>
tmp = tmp & rst![YearID]<br>
rst.MoveNext<br>
Wend<br>
StrungOut = tmp<br>
Set rst = Nothing<br>
Set dbs = Nothing<br>
End Function<br>