Display multiple records on one line in a report.

[DBAchick]

The records in my table look like this:<br><br>GroupName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data1&nbsp;&nbsp;&nbsp;Data2&nbsp;&nbsp;&nbsp;&nbsp;Date3<br><br>Group1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XYZ<br>Group1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Apple<br>Group1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Zebra<br>Group1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QRS<br>Group2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ABC<br>Group3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LMN<br>Group3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tiger<br><br>I want my report to look like this:<br><br>GroupName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data1&nbsp;&nbsp;&nbsp;&nbsp;Data2&nbsp;&nbsp;&nbsp;&nbsp;Data3<br><br>Group1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XYZ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Apple&nbsp;&nbsp;&nbsp;&nbsp;Zebra<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QRS<br>Group2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ABC<br>Group3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LMN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tiger<br><br>I have tried grouping, using group headers and footers, but each record appears on a separate line.&nbsp;&nbsp;In addition, the GroupName appears on a separate line.&nbsp;&nbsp;<br><br>I have tried subreports, but they are not dynamic enough.<br><br>I have tried a crosstab query but the results are less than correct. (Perhaps I am doing that incorrectly).<br><br>Any report genuises out there?<br>

 

[DougP]

Well it looks like a job for your friend VBA Code.<br>And you CAN do this by creating a new table which holds the results of your findings.&nbsp;&nbsp;It looks as though you've exhausted the regular ways. <br>Then the report is a regular report.<br>So first you have to open a recordset and build a base of records.<br>Using your example<br>Your new table would have 4 fields named, <br>&quot;Groupname&quot; ,&quot;Data1&quot;, &quot;Data2&quot; and &quot;Data3&quot;<br>You open a recordset and in a for next loop examine the records<br>-------------------------------------<br>Public Function sample()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(&quot;YourTable&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;Open YourFile For Output As #1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;For A = 1 To rst.RecordCount<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Print #1, rst!Field1 & &quot;, &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Print #1, rst!Field2 & &quot;, &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Print #1, rst!Field3 & &quot;, &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst, rstNew As recorsdset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim A As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(&quot;yourExistingTable&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rstNew = db.OpenRecordset(&quot;yourNewTable&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveLast<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;For A = 1 To rst.RecordCount<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstNew.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstNew!Groupname = rst!Groupname<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Something = SomeValue Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstNew!Data1 = rst!SomeField<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Something2 = SomeValue2 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstNew!Data2 = rst!SomeField2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Something3 = SomeValue3 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rstNew!Data3 = rst!SomeField3<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;rstNew.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;db.Close<br><br>End Function<br>---------------------------<br>Here's a crude example<br>basically you open your exisitng table and then you add records to your new table so they end up looking like your Example at the top.<br><br>Now you may have more than one loop<br>so you build a staring ste of data and then add to it.<br>I've done this kind of thing when the data is so out-ta-wack<br><br>It runs really fast once it's done and can blast through thousands of records in the blink of an eye.<br>Though getting the code exactly right might take a while.<br><br>OK <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.