Queries: Showing All Sub-entries with Parent Entries as One Line Item

With "Child" Information Intact

Overview

This is a big breakthrough, the first big breakthrough in a long time, and one of the biggest ever. The scenario: being able to show one-to-many entries as 1line item when browsing the "parent" table.

This is a HUGE deal, because it helps me convert Excel spreadsheets over to Access much easier, and makes it far less tempting to short-cut around the "one to many" relationships setup. The inability to display information this way has been a HUGE pest in the past, and made many office workers stick with Excel as a database.

How to Fix It

Note: this only works for calling one field at a time for each expression. If you need to call, say, first name & last name with 1 expression & "concatenate" them as a "full name," skip this section, the syntax of the code & field syntax are different & are below.

(1) Create a new public module, save as mod_Create_CVS (or the like), copy-paste the following code:

Function CombineChildRecords(strTblQryIn As String, _ strFieldNameIn As String, strLinkChildFieldNameIn As String, _ varPKVvalue As Variant, Optional strDelimiter) As Variant Dim db As DAO.Database Dim qd As DAO.QueryDef Dim rs As DAO.Recordset Dim strSQL As String Dim varResult As Variant Set db = CurrentDb Set qd = db.CreateQueryDef("") 'You can edit the ", " portion of the next line to change what value separates 'the different 'child' values, it's currently set to be the comma (,) character If IsMissing(strDelimiter) Then strDelimiter = ", " strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]" qd.SQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]" qd.Parameters("ParamIn").Value = varPKVvalue Set rs = qd.OpenRecordset() Do Until rs.EOF varResult = varResult & rs.Fields(strFieldNameIn).Value & strDelimiter rs.MoveNext Loop rs.Close If Len(varResult) > 0 Then varResult = Left$(varResult, _ Len(varResult) - 2) CombineChildRecords = varResult Set rs = Nothing Set qd = Nothing Set db = Nothing End Function

(2) Create a query based on the parent table

(3) Create a custom field in the query, with the following syntax:

NewFieldName: CombineChildRecords("child_table","child_field_whose_values_you_wish_to_display","child_to_parent_link_id",[parent_table_autoid_field])

An example:

("Parent" table is called "tblParents", "child" table is called "tblChildren" which links to the parent table with the field "parent_id", the parent table's autoid field is "autoid")

st_children: CombineChildRecords("tblChildren","child_name","parent_id",[autoid])

Multiple Field Alternative

The above example only works if you are calling one field for a given expression. If you have more than 1 (example, first-name and last-name and you wish to concatenate them as a "full name") the function code & syntax for calling it are slightly different. (The above syntax probably works fine for calling more than 1 field, if you call each field with a separate expression.)

I got this from this UtterAccess posting of mine (which followed up on--and links to--the earlier one.I am told this works fine for calling one field, as well--but haven't tried it and/or tried out how the syntax for the equation would work.)

The new module:

Function CombineChildRecords(strTblQryIn As String, _
       strFieldNamesIn As String, strLinkChildFieldNameIn As String, _
       varPKVvalue As Variant, Optional strDelimiter As String = "; ", _
       Optional strSeparator As String = "") As Variant
      
          Dim db As DAO.Database
          Dim qd As DAO.QueryDef
          Dim rs As DAO.Recordset
          Dim strSQL As String
          Dim varResult As Variant
          Dim arrFields() As String
          Dim lngIndex As Long
            
          Set db = CurrentDb
          Set qd = db.CreateQueryDef("")
      
          arrFields = Split(strFieldNamesIn, ",")
          
          strSQL = "SELECT "
          
          For lngIndex = 0 To UBound(arrFields)
            If lngIndex > 0 Then strSQL = strSQL & ","
            strSQL = strSQL & "[" & arrFields(lngIndex) & "]"
          Next lngIndex
          strSQL = strSQL & " FROM [" & strTblQryIn & "]"
          qd.SQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]"
          qd.Parameters("ParamIn").Value = varPKVvalue
          
          Set rs = qd.OpenRecordset()
          
          Do Until rs.EOF
            For lngIndex = 0 To UBound(arrFields)
              If lngIndex > 0 Then varResult = varResult & strSeparator
              varResult = varResult & rs.Fields(arrFields(lngIndex)).Value
            Next lngIndex
            varResult = varResult & strDelimiter
            rs.MoveNext
          Loop
      
          rs.Close
      
          If Len(varResult) > 0 Then varResult = Left$(varResult, _
       Len(varResult) - Len(strDelimiter))
      
          CombineChildRecords = varResult
      
          Set rs = Nothing
          Set qd = Nothing
          Set db = Nothing
       End Function

The syntax of how you'd call it: