Grouping Data (1 Viewer)

[DBL]

I posted a bit about this earlier in the week but didn't get any responses. I don't think my explanation of the problem was very good! Here's another go which I hope might be clearer:

I have a database that records samples received into a lab. This includes the sample type and the area/region it's come from:

Material Region
Sliced portions of brain Frontal
Sliced portions of brain Parietal
Sliced portions of brain Temporal
Sliced portions of brain Occipital
Sliced portions of brain Cerebellum
Sliced portions of brain Basal Ganglia
Sliced portions of brain Thalamus
Organ sample Pituitary


To enable the data to be exported to a Word template for reporting purposes, I have to string all the information together so it goes out as a paragraph and is placed using a Bookmark. However the code I'm currently using is giving me the data like this:

Sliced portions of brain, Frontal; Sliced portions of brain, Parietal; Sliced portions of brain, Temporal; Sliced portions of brain, Occipital; Sliced portions of brain, Cerebellum; Sliced portions of brain, Basal Ganglia; Sliced portions of brain, Thalamus; Organ Sample, Pituitary.

This is the code I'm using to get it like this is:

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!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

Ideally I want the data to show as:

Sliced portions of brain: Frontal, Parietal, Temporal, Occipital, Cerebellum, Basal Ganglia, Thalamus;
Organ Sample, Pituitary.

I know that I need to create two recordsets and open the first one, the MaterialType, and then loop through and add that Material Type and the associated areas to the string before moving on to the next material type but I just can't get it to work.

If you had any thoughts on the matter I'd love to hear them.

 

[DBL]

I even re-wrote is as DAO and I'm getting better results although this still isn't right as it's looping through infinitely!

Dim stDocName As String
Dim strdata As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs As DAO.Recordset
Dim Answer As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qrySamplesReceived WHERE [MatRecCase] = " & CaseID, dbOpenDynaset)
Set rs1 = db.OpenRecordset("SELECT * FROM qryFixedReceived", dbOpenDynaset)

Stop

While Not rs.EOF

On Error Resume Next

If rs.RecordCount >= 1 Then
'rs.MoveFirst
Answer = rs!MaterialType & " - "
'rs1.MoveFirst

rs1.Filter = "MaterialType = '" & rs!MaterialType & "'"


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

rs1.MoveNext
Next i
rs1.Filter = ""

FixedReceived = Left(Answer, Len(Answer) - 2)
End If
rs.MoveNext
Wend


rs1.Close
rs.Close

Set db = Nothing
Set rs1 = Nothing
Set rs = Nothing

 

[twoplustwo]

I would use one query and just split the material from the region. Pass each piece of data to a variable i.e. strMaterial and strRegion.

For i = 1 To rs1.RecordCount
If i = 1 then
Output strMaterial
Else
Output strRegion
End if
Next i

 

[spikepl]

Your data is a bit messy, in that you have different type of information in the same column ("Organ Sample" which pertains to the the rest) but no marker to indicate that that is the case (last line?) Normally that would be two different tables, one for Sample Types and the other one for Material/Region, with a Foreign Key (from tblSampleTypes) indicating which SampleType.

I could in fact imagine another two tables - tblRegions and tblMaterials

Update: In any case, when posting code use the code brackets (->Advanced ->#) otherwise the code is difficult to read.

 

[DBL]

Sorry, just the way I've presented it here. It should have read:

Sliced portions of brain: Frontal, Parietal, Temporal, Occipital, Cerebellum, Basal Ganglia, Thalamus;
Organ Sample: Pituitary.

The data is split into different tables as you've suggested, I'm just concatinating it for reporting purposes.

Apologies, haven't used code brackets before so I'll look into that for future.

Other than that, any suggestions?