ms-access: query (concat multiple records into one)

Question

here's a glimpse of the original table:

Occurrence Number   Occurrence Date 1 0 Preanalytical (Before Testing)  Cup Type    2 0 Analytical (Testing Phase)  2 0 Area    3 0 Postanalytical ( After Testing) 4 0 Other   Practice Code   Comments
1477    2/5/2010    1.1 Specimen Mislabeled                     PURSLEY 
1476    2/5/2010    1.1 Specimen Mislabeled                     HPMR    
1475    2/5/2010    1.1 Specimen Mislabeled                     ACCIM   N008710
1474    2/5/2010    1.1 Specimen Mislabeled                     ACCIM   N008636
1473    2/5/2010    1.3 QNS-Quantity Not Sufficient                     SAPMC   
1472    2/5/2010    1.3 QNS-Quantity Not Sufficient                     RMG 
1471    2/5/2010    1.1 Specimen Mislabeled                     NMED    
1470    2/5/2010    1.9 QNS- Specimen Spilled in transit                        MRPS    
1469    2/5/2010    1.9 QNS- Specimen Spilled in transit                        ANESPC  
1468    2/5/2010            2.22 Instrument Problem-reinject                LAB 
1525    2/8/2010            2.5 Other - False (+) Blanks    Tecan 2         LAB 
1524    2/8/2010            2.5 Other - False (+) Blanks    Tecan #1            LAB Blank 019
1523    2/8/2010            2.22 Instrument Problem, 2.5 Other  Tecan           LAB 
1519    2/8/2010                    3.3A Reporting Error    4.1 LIS Problem? (see LOM 1418,1520)    LAB/SJC F356028
1518    2/8/2010    1.4 Tests Missed/Wrong Test Ordered                     SDPTC   F316628
1516    2/8/2010    1.6 Test Requisition Missing                        TPMCF   2 specimens both unlabeled
1515    2/8/2010    1.1 Specimen Mislabeled                     PALMETTO    
1514    2/8/2010    1.1 Specimen Mislabeled                     THWR    
1513    2/8/2010    1.1 Specimen Mislabeled                     THWR    

i am getting information from this table using the following statement:

select mid(Lom1,1,4) as LOM, sum([Count1]) as [Count] from (

SELECT [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] as Lom1,Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [Count1]
FROM [Lab Occurrence Form]
WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between [Forms]![Meeting_Reasons_Frequency]![Text4] And [Forms]![Meeting_Reasons_Frequency]![Text2]))
GROUP BY [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]
HAVING Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)])<>0

UNION SELECT [Lab Occurrence Form].[2 0 Analytical (Testing Phase)], Count([Lab Occurrence Form].[2 0 Analytical (Testing Phase)]) AS [CountOf2 0 Analytical (Testing Phase)]
FROM [Lab Occurrence Form]
WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between [Forms]![Meeting_Reasons_Frequency]![Text4] And [Forms]![Meeting_Reasons_Frequency]![Text2]))
GROUP BY [Lab Occurrence Form].[2 0 Analytical (Testing Phase)]
HAVING Count([Lab Occurrence Form].[2 0 Analytical (Testing Phase)])<>0

union

SELECT  [Lab Occurrence Form].[3 0 Postanalytical ( After Testing)], Count([Lab Occurrence Form].[3 0 Postanalytical ( After Testing)]) AS [CountOf3 0 Postanalytical ( After Testing)]
FROM [Lab Occurrence Form]
WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between [Forms]![Meeting_Reasons_Frequency]![Text4] And [Forms]![Meeting_Reasons_Frequency]![Text2]))
GROUP BY  [Lab Occurrence Form].[3 0 Postanalytical ( After Testing)]
HAVING Count([Lab Occurrence Form].[3 0 Postanalytical ( After Testing)])<>0

UNION SELECT [Lab Occurrence Form].[4 0 Other], Count([Lab Occurrence Form].[4 0 Other]) AS [CountOf4 0 Other]
FROM [Lab Occurrence Form]
WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between [Forms]![Meeting_Reasons_Frequency]![Text4] And [Forms]![Meeting_Reasons_Frequency]![Text2]))
GROUP BY [Lab Occurrence Form].[4 0 Other]
HAVING Count([Lab Occurrence Form].[4 0 Other])<>0
ORDER BY 1, 2)

group by mid(Lom1,1,4);

this is what the query returns:

LOM Count
1.1     231
1.11    21
1.3     103
1.4     6
1.5     1
1.6     25
1.8     2
1.9     88
2.1     8
2.22    5
2.24    1
2.3     1
2.4     1
2.5     29
3.2     13
3.3     8
3.3A    4
4.1     2
4.6     1
4.8     7

i need to add another column here. let's say it is column3

this is the output that need:

LOM Count   column3
1.1     231 everything from original table where LOM LIKE *1.1* separated by ","
1.11    21  everything from original table where LOM=1.11 separated by ","
1.3     103 everything from original table where LOM=1.3 separated by ","
1.4     6   everything from original table where LOM=1.4 separated by ","
1.5     1   everything from original table where LOM=1.5 separated by ","
1.6     25  
1.8     2   
1.9     88  
2.1     8   
2.22    5   
2.24    1   
2.3     1   
2.4     1   
2.5     29  
3.2     13  
3.3     8   
3.3A    4   
4.1     2   
4.6     1   
4.8     7   
prac    1   

that would mean the first element in column 3 would be "something1, something2, etc...somethingelse231"

i apologize if this explanation is horrible, please let me know if i can clarify anything

Answer 1

Here's one solution I found: http://www.access-programmers.co.uk/forums/showpost.php?p=272455&postcount=2

It requires writing a VBA function. I don't know of a way to do it with straight SQL in Access.

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim SQL As String
  Dim vFld As Variant

  Set cnn = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  vFld = Null

  SQL = "SELECT [" & Fieldx & "] as Fld" & _
        " FROM [" & Source & "]" & _
        " WHERE [" & Identity & "]=" & Value

  ' open recordset.
  rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

  ' concatenate the field.
  Do While Not rs.EOF
    If Not IsNull(rs!Fld) Then
      vFld = vFld & ", " & rs!Fld
    End If
    rs.MoveNext
  Loop
  ' remove leading comma and space.
  vFld = Mid(vFld, 3)

  Set cnn = Nothing
  Set rs = Nothing

  ' return concatenated string.
  Conc = vFld
End Function

You can then use it in a query like this:

SELECT [tblData].[ID], 
       Conc("Field1","ID",[ID],"tblData") AS Field1,
       Conc("Field2","ID",[ID],"tblData") AS Field2
FROM tblData
GROUP BY [tblData].[ID];

Edit So your first query would look something like this:

SELECT [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] as Lom1,
       Conc("NameOfTheFieldToConcatenate", 
            "[Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]", 
            [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)],
            "[Lab Occurrence Form]"),
       Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [Count1]
FROM [Lab Occurrence Form]
WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between [Forms]![Meeting_Reasons_Frequency]![Text4] And [Forms]![Meeting_Reasons_Frequency]![Text2]))
GROUP BY [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]
HAVING Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)])<>0

Note that you may have to tweak the Conc() function a little to get the wildard compare you want instead of an exact match on the LOM field.