Go Premium for a chance to win a PS4. Enter to Win


nested concatenate functions in sql statement for Access query

Posted on 2010-11-24
Medium Priority
Last Modified: 2012-05-10
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

Question by:ksilvoso
  • 5
  • 3
  • 2
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34205713
If you post a sample, you should also include the steps to replicate the issue.
Sound fair?

LVL 22

Expert Comment

ID: 34206069
Sounds fair to me. ;-)
LVL 22

Expert Comment

ID: 34206079
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 ;-).
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Author Comment

ID: 34209681
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
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34210708

Can you just show us the *exact* output you are looking for? (From James Trimble)

Author Comment

ID: 34212092
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
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 34214112
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, ...it 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

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34214116
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...


Author Comment

ID: 34223901
   Thanks for your sample database.  I will use your method of running vba code behind the report as you suggested.  
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34227114

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.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question