How to write a query in MS Access 2007 to display more than one value in more than one column?

Question

I have a table.

Table Name:tblRAC

Examiner Subj scheme pap_code moderator pap_accessed  pap_moderated
Kamble   DTE   EJ1E   12116    mahulkar     25            5
kamble   AEL   DE4E   12110    Patil        2             2
kamble   DTE   CO3C   12064    Nale         37            20
kamble   DTE   IF3E   12064                 01            
Kamble   DTE   IL1E   12116    kulkarni     35            5

How to write a query in MS Access 2007 to get output as below.

subj    pap_Code   scheme      pap_accessed moderator
DTE      12116    EJ1E,IL1E     60          mahulkar,kulkarni
DTE      12064    CO3C,IF3E     38          Nale
AEL      12110    DE4E          02          Patil

                    Total  100    

Answer 1

I actually decided to post this as an answer instead of a comment to make it more easily accessible.

What you want is exactly what other DBMS's provide (which I don't really like to use) that is called GROUP_CONCAT. I edited your title to make that more apparent to future searchers(hope you don't mind).

Since Access doesn't support GROUP_CONCAT in SQL syntax, you could either write some funky SQL to hope to replicate it or just use VBA.

Below is an example GROUP CONCAT VBA function that you can use. found here (I didn't write it).

  Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ", _
        Optional pstrLastDelim As String = "") _
        As Variant
'   Created by Duane Hookom, 2003
'   this code may be included in any application/mdb providing
'   this statement is left intact
'   example
'   tblFamily with FamID as numeric primary key
'   tblFamMem with FamID, FirstName, DOB,...
'   return a comma separated list of FirstNames
'   for a FamID
'    John, Mary, Susan

'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =" & [FamID]) as FirstNames
'   FROM tblFamily
'   ============================================

'   to get a return like Duane, Laura, Jake, and Chelsey

'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =" & [FamID], ",",", and ") as FirstNames
'   FROM tblFamily
'   ============================================

'   If FamID is a string rather than numeric,
'    it will need to be delimited with quotes

'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =""" & [FamID] & """", ",",", and ") as FirstNames
'   FROM tblFamily
'   ============================================


'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    'Dim rs As New ADODB.Recordset
    'length before last concatenation
    Dim intLenB4Last As Integer
    'rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                intLenB4Last = Len(strConcat)
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
        If Len(pstrLastDelim) > 0 Then
            strConcat = Left(strConcat, _
                intLenB4Last - Len(pstrDelim) - 1) _
                & pstrLastDelim & Mid(strConcat, intLenB4Last + 1)
        End If
    End If
    If Len(strConcat) > 0 Then
        Concatenate = strConcat
     Else
        Concatenate = Null
    End If
End Function