Grouped Report 1

[Ouch]

I have a report that gives authors and associated records...
like so:-

Booth D 13
Booth D 22
Booth D 23
Eston R G 26
Eston R G 27
Eston R G 265
Eston R G 267


is there a way i can get it to display like this?

Booth D 13,22,23
Eston R G 26,27,265,267

any help would be very much appreciated

 

[ClydeDoggie]

You can use VBA to open the recordset currently behind the report and create some strings to place in a temp table that the report will now be based on. If you aren't comfortable with VBA and recordset operations let me know and I will show you an example of what I mean.

 

[Ouch]

could you give me an example please?

 

[ClydeDoggie]

Assuming you have a query (I'll call it qryAuthTotals) that returns the records you show, here is what I would do.
Create a table in your database named tblTempReport with three fields, an ID field, an AuthorName field, and a AssocRecords field.
Add this code to your report:

Private Sub Report_Open(Cancel as Integer)

Dim sSQL as String
Dim db as Database
Dim rstTo as Recordset
Dim rstFrom as Recordset
Dim sAuthor as String
Dim sTotals as String

set db=CurrentDB

'Open two recordsets, one with the records to be collated, one empty one used to add records to temp table
sSQL="SELECT * from qryAuthTotals"
set rstFrom=db.OpenRecordset(ssql)
sSQL="SELECT * from tblTempReport"
set rstTo=db.OpenRecordset(ssql)

'Loop through From recordset, when author changes write strings into temp table
Do While Not rstFrom.eof
If sAuthor ="" Then sAuthor=rstFrom!Author
if sAuthor<>rstFrom!Author then
rstTo.AddNew
rstTo!AuthorName=sAuthor
rstTo!AssocRecords=Left(sTotals, Len(sTotals)-1))
rstTo.Update
sAuthor=rstFrom!Author
sTotals=&quot;&quot;
End If
sTotals=sTotals & rstFrom!Totals & &quot;, &quot;
rstFrom.MoveNext
Loop

'Housekeeping
set rstFrom=Nothing
set rstTo=nothing
set db=Nothing

'set report recordsource to new temp table
Me.Recordsource=&quot;Select * from tblTempReport&quot;


This is in A97, if you are using A2K and ADO you will have to modify the recordset code slightly. Also, make sure you create a delete query for the temp table and run it before opening the report so the old data is cleared.

 

[Ouch]

Thank you.....Works a treat....

complete code for ADO (i have Access 2002)

Set dbs = CurrentDb
Dim sSQL As String
Dim rstTo As New ADODB.Recordset
Dim rstFrom As New ADODB.Recordset
Dim sAuthor As String
Dim sTotals As String



'Open two recordsets, one with the records to be collated, one empty one used to add records to temp table
sSQL = &quot;SELECT * from qry_auth_index&quot;

rstFrom.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText

sSQL = &quot;SELECT * from tblTempReport&quot;
rstTpen sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText

'Loop through From recordset, when author changes write strings into temp table
Do While Not rstFrom.EOF
If sAuthor = &quot;&quot; Then sAuthor = rstFrom!data
If sAuthor <> rstFrom!data Then
rstTo.AddNew
rstTo!AuthorName = sAuthor
rstTo!AssocRecords = Left(sTotals, Len(sTotals) - 1)
rstTo.Update
sAuthor = rstFrom!data
sTotals = &quot;&quot;
End If
sTotals = sTotals & rstFrom!bibref_id & &quot; &quot;
rstFrom.MoveNext
'MsgBox (sAuthor)
Loop

'Housekeeping
Set rstFrom = Nothing
Set rstTo = Nothing
Set db = Nothing

'set report recordsource to new temp table
Me.RecordSource = &quot;Select * from tblTempReport&quot;