nested concatenate functions in sql statement for Access query

I need sql code to concatenate within a concatenate to prevent duplication of records.

Directly below is a report sample.  Briefly explained, Ancestor (Abernathy, John, Sr.) has several children; one of those children (Charles) has 2 wives, __Harwell and Elizabeth Davenport.  As you can see below this is causing Charles to appear twice as 2 separate children.


Abernathy, John, Sr.  (PS - VA) (1723 - 1812) m  Lucy  1744
Children: Ann  m. __ Harwell; Buckner  m. Hulda Rivers; Charles  m. __ Harwell; Charles  m. Elizabeth Davenport; Frederick 1745 m. Milly Davenport; Frederick 1745 m. Tabitha Harwell; Herbert  m. Susannah Harwell; Jane  m. John Davis; John, Jr ca
1743 m. Mary __; Joshua  ; Robert  m. Abagail __; Smith  

My sql statement calls a concatenate module. How can I change the syntax of my sql code so that Charles will only appear once but with both wives listed like so:
Charles  m. __ Harwell, m. Elizabeth Davenport;

Here is the sql code for the report:
SELECT [ANCESTOR INFORMATION].AncestorCID, "Children:" & " " & Nz(DConcat("[Child First Name],[child date of birth], [Child Spouse Name]","[qryancestorchildren]","[Ancestorcid] = " & [Ancestorcid] & "","; "," ",True," [Child First Name] ASC "),"") AS Children, Nz(DConcat("' m ',[ancestor wife], [ancestor wife surname], [marriage date]","[jcnAncestorWife]","[AncestorCID] = " & [AncestorCID]),"") AS ListSpouses, [Ancestor Information]![surNAME] & "," & " " & [Ancestor Information]![first name] & " " & [Ancestor Information]![MAIDEN NAME] & " (" & [ancestor information]![service] & " - " & [Ancestor Information]![STATE SERVED] & ") (" & [Ancestor Information]![DATE OF BIRTH] & " - " & [Ancestor Information]![DATE OF DEATH] & ")" & [listSpouses] AS List, Nz(DConcat("[MemberFName], [membermiddlename],[membersurname]","[qryAncestorMember]","[AncestorCID] = " & [AncestorCID]),"") AS Members, [ANCESTOR INFORMATION].[FIRST NAME], [ANCESTOR INFORMATION].SURNAME, [ANCESTOR INFORMATION].[ANCESTOR NUMBER]

this is the relevant section of the above:
Children: "Children:" & " " & Nz(DConcat("[Child First Name],[child date of birth], [Child Spouse Name]","[qryancestorchildren]","[Ancestorcid] = " & [Ancestorcid] & "","; "," ",True," [Child First Name] ASC "),"")

I have attached my concatenate function, (thanks MatthewsPatrick) and also the database.  The query I'm referring to is qryAncestoryFamily

Option Compare Database
Option Explicit

Function DConcat(ConcatColumns As String, tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = "; ", Optional Delimiter2 As String = " ", _
    Optional Distinct As Boolean = True, Optional OrderBy As String = "", _
    Optional Limit As Long = 0)
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    ' Requires reference to Microsoft DAO library
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' OrderBy (optional) indicates how to sort the results.  The argument value should consist
    '   of an entire ORDER BY clause (without the actual words "ORDER BY"), or be omitted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    On Error GoTo ErrHandler
    ' Initialize to Null
    DConcat = Null
    ' Build up a query to grab the information needed for the concatenation
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
           IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
       "FROM " & tbl & " " & _
      IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
      IIf(Trim(OrderBy) <> "", "ORDER BY " & OrderBy, "")
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF
            ' Initialize variable for this row
            ThisItem = ""
            ' Concatenate columns on this row
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            ' Trim leading delimiter
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            ' Concatenate row result to function return value
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
    End With
    ' Trim leading delimiter
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    GoTo Cleanup

    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    DConcat = CVErr(Err.Number)
    Set rs = Nothing
End Function

Open in new window

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
I will admit that I am a bit confused by your Table structures and your query output.
So my first question would be: Are you quite sure your database is set up in the most efficient way to present the data in the manner you are requesting?

As far as I can tell, this is a "Pedigree" type database.
So see here for reference:

What's also complicating things here is that you are not only trying to show one ancestor, who they married, and the Children sired, ...but also show who the children married as well, (and birth years, and other info!)...
In other words, (IMHO), the top level output should only show the most basic info on the one Ancestor, and who they married, and the Childrern sired.)

It is also not clear what the final, ultimate output will be, ...a query or a report.
If the final output is a query, then it is not clear how you might interrogate these "Concatenated" fields.
If the final output will be a Report, then, ...if it were me, I would do my concatenation there.

The other issue with concatenating this data in the query is that you don't immediately know what the various sections are, (or even if a value is missing)
For example If I saw this:
    Jeff Coachman, 1945
How would I know if the 1945 was the Birth date, marrige date, death date, ...etc
In your db, I believe that it means the birth date for the ancestor, but in the case of the Wife, it means the married date?
(In any event, still holds true that it is unclear what each segment represents)

(I am confused about other things as well, but I will stop here)

So here I would be concerned with the Data structure *first* then worry about concatenating strings later.

In any event, since I am not intimately familiar with the:
Requirements of this system
Structure of this system
Final output format or vehicle.

I will post an example of how I might handle something like this, with minimal top level info.
(You can expand out from there)
See the attached sample DB

Jeffrey CoachmanMIS LiasonCommented:
If you post a sample, you should also include the steps to replicate the issue.
Sound fair?

Sounds fair to me. ;-)
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

If nothing else, create a separate database with just enough data, etc., so that we can fiddle with your query (swhich should also be included in the stripped down database ;-).
ksilvosoAuthor Commented:
I'm attaching the database again in a pared down version.
Run the query, qryAncestoryFamily. The 4th record down is James Trimble. The Children column shows James' 4 children (David, Jane, John and Robert) and who they married, ex;David 1775 m. Polly Zelfro:  David had 2 wives, Polly and Charity so he appears twice as though he were 2 different people like so:
Children: David 1775 Charity Bowman; David 1775 m. Polly Zelfro;

. This is what I want to change.   This is the sql code for the Children column:

Children: "Children:" & " " & Nz(DConcat("[Child First Name],[child date of birth], [Child Spouse Name]","[qryancestorchildren]","[Ancestorcid] = " & [Ancestorcid] & "","; "," ",True," [Child First Name] ASC "),"")

Is it possible to nest one concatenate within another to accomplish this or perhaps there's a better way.  Thanks for any help you can give me on this, Karen
Jeffrey CoachmanMIS LiasonCommented:

Can you just show us the *exact* output you are looking for? (From James Trimble)
ksilvosoAuthor Commented:
For the children column, instead of:
Children: David 1775 Charity Bowman; David 1775 m. Polly Zelfro; Jane  m. John Northcutt; John 1794 m. Margaret Thurley; Robert 1793 m. Elizabeth Young

I need it to be:
Children: David 1775 m. Charity Bowman, m. Polly Zelfro; Jane  m. John Northcutt; John 1794 m. Margaret Thurley; Robert 1793 m. Elizabeth Young
Jeffrey CoachmanMIS LiasonCommented:
As a side topic, I am not sure how your database deals with "Modern" families:
Two Fathers/Mothers
Single Parent Households
"Blended" Families (two divorced Parents with their own children)
Adopted Children
Children who have had Grandparents or other family members as Legal guardians since birth
Unknown Father
Parent name change
Mother Name change (Married Name, Divorced name, Hyphenated maiden married names, ...etc)
If the Child lineage is still tracked for "Mothers" if, and after, they remarry

...but these are a separate topics altogether...

ksilvosoAuthor Commented:
   Thanks for your sample database.  I will use your method of running vba code behind the report as you suggested.  
Jeffrey CoachmanMIS LiasonCommented:

You can still use the handy Dconcat function to concatenate your values, just in the report instead of in the query.

And I would also posts a question on your table structures (siting my "Pedigree" links) to verify if the design is appropriate.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.