Results 1 to 5 of 5
  1. #1
    jaherrick is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    2

    "Rollup", or multiple results in one column

    My database has a table for people, a table for events, and a junction table for involvement (which links an each event to a more than one person and each person to more than one event)
    I want to get a query of all the people who attended a particular event in a single row, comma separated.

    Right now I know how to get this:
    EVENT PERSON
    2018 National Conference 17
    2018 National Conference 23
    2018 National Conference 35


    Luncheon 1 17
    Luncheon 2 54

    Instead, I want this:

    EVENT PERSON
    2018 National Conference 17, 23, 35
    Luncheon 1 17
    Luncheon 2 54

    Any tips?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can do it easily with a Crosstab query, but not the way you show it in your example.
    To do that will take some programming, since data doesnt flow that way.

  3. #3
    jaherrick is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    2
    Can you elaborate on how to do it with a crosstab query?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Generating the example output accomplished with VBA. Review http://allenbrowne.com/func-concat.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I have a some sample VBA code at http://forestbyte.com/vba-code-samples/ - look for JoinFromArray function. You can call that from any query. I am also pasting the code here (add it to a standard module):

    Code:
    Attribute VB_Name = "modJoinFromArray"
    Option Compare Database
    Option Explicit
    Public Function vcJoinFromArray(vID, sUniqueIDName As String, sIDDataType As String, sQueryName As String, sFieldNames As String, sFieldNamesDelimiter As String, Optional sJoinDelimiter As String)
    
    
    Dim db As DAO.Database, rst As DAO.Recordset, strSQL As String
    Dim sFields() As String, strSelectedFields, iCount As Integer, vChar
    Dim arrData As Variant
    
    
    Set db = CurrentDb
    
    
    sFields = Split(sFieldNames, sFieldNamesDelimiter)
    
    
    For iCount = 0 To UBound(sFields)
        strSelectedFields = strSelectedFields & "[" & sQueryName & "].[" & sFields(iCount) & "],"
    Next iCount
    
    
    If Right(strSelectedFields, 1) = "," Then strSelectedFields = Left(strSelectedFields, Len(strSelectedFields) - 1)
    
    
    If sIDDataType = "TEXT" Then vChar = Chr(39)
    If sIDDataType = "NUM" Then vChar = ""
    If sIDDataType = "DATE" Then vChar = Chr(35)
    
    
    strSQL = "SELECT " & strSelectedFields & " FROM [" & sQueryName & "] WHERE [" & sQueryName & "].[" & sUniqueIDName & "] =" & vChar & vID & vChar & ";"
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    
    If rst.RecordCount = 0 Then
        vcJoinFromArray = ""
        Exit Function
    End If
        With rst
            .MoveLast
            .MoveFirst
            arrData = .GetRows(.RecordCount)
        End With
    
    
    arrData = TransposeArray(arrData)
    
    
    arrData = vcConvertToOneDimArray(arrData)
    
    
    Dim x
    x = sJoinDelimiter
    
    
    vcJoinFromArray = Join(arrData, IIf(sJoinDelimiter = "", " | ", sJoinDelimiter))
    Set rst = Nothing
    Set db = Nothing
    
    
    End Function
    
    
    Function TransposeArray(v As Variant) As Variant
    ' Transpose dimensions of a 0-based
    ' multi-dimensional array
    
    
        Dim x As Long, Y As Long, Xupper As Long, Yupper As Long
        Dim tempArray As Variant
    
    
        Xupper = UBound(v, 2)
        Yupper = UBound(v, 1)
    
    
        ReDim tempArray(Xupper, Yupper)
        For x = 0 To Xupper
            For Y = 0 To Yupper
                tempArray(x, Y) = Trim(v(Y, x))
            Next Y
        Next x
    
    
        TransposeArray = tempArray
    
    
    End Function
    Function vcConvertToOneDimArray(a As Variant) As Variant
        Dim row As Integer, col As Integer
        Dim Temp As String
        Dim tempArray() As Variant, Xupper As Long, Yupper As Long
        
        ReDim tempArray(UBound(a))
        For row = LBound(a) To UBound(a)
        Temp = ""
            For col = LBound(a, 2) To UBound(a, 2)
                Temp = Temp & " " & Trim(a(row, col)) & " "
            Next col
            tempArray(row) = Trim(Temp)
        Next row
        
        vcConvertToOneDimArray = tempArray
    End Function
    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 1
    Last Post: 05-20-2015, 09:53 AM
  3. Replies: 6
    Last Post: 11-18-2013, 07:52 AM
  4. Column sum works in "Detail" but not "Footer"
    By Doodlebug2000 in forum Reports
    Replies: 1
    Last Post: 12-10-2012, 03:20 PM
  5. Replies: 3
    Last Post: 03-21-2011, 05:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums