Grouping Data

[DBL]

Background: I have a form that logs multiple rows of data for each case:

Material Option, Material Type and Area

for reporting purposes I use this code to loop through the records and create a string so that the information can be output as a mail merge bookmark to a Word document template:

Dim strSQL1 As String
Dim Answer As String
Dim i As Integer
Dim rs As New ADODB.Recordset

On Error GoTo HandleErr

strSQL1 = "SELECT qryFrozenReceived.MatRecCase, qryFrozenReceived.MatOption, qryFrozenReceived.MaterialType, qryFrozenReceived.Area " _
& " FROM qryFrozenReceived " _
& " WHERE qryFrozenReceived.MatRecCase = " & CaseID & ""

rs.Open strSQL1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.RecordCount >= 1 Then
rs.MoveFirst
Answer = rs!MatOption & ": " & rs!MaterialType & " - "
rs.MoveFirst
MatType = rs!MaterialType


For i = 1 To rs.RecordCount
Answer = Answer & (rs!Area) & ", "

rs.MoveNext
Next i

FrozenReceived = Left(Answer, Len(Answer) - 2)
End If

Previously I had a trimmed down version of this code which gave me the results as follow:

Option 1, Type 1, Area 1; Option 1, Type 1, Area 2; Option 1, Type 1, Area 3

Ideally I want it to show as:

Option 1, Type 1, Area 1, Area 2, Area 3; Option 1, Type 2, Area 1, Area 2; Option 2, Type 1, Area 1, Area 2, Area3

The amended code above is giving me:

Option 1, Type 1, Area 1, Area 2, Area 3, Area 1(with this Area 1 being a different Type (Type2).

Don't know if any of this makes sense! Basically I need it to loop through and then the Material Type changes, put in the new material type and the associated areas.

Any ideas?