Solved

nested concatenate functions in sql statement for Access query

Posted on 2010-11-24
10
905 Views
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.

REPORT SAMPLE:

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]
FROM [ANCESTOR INFORMATION]
WHERE ((([ANCESTOR INFORMATION].[ANCESTOR NUMBER])>"7000" And ([ANCESTOR INFORMATION].[ANCESTOR NUMBER])<"7050"))
ORDER BY [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
Thanks,
Karen


 ForEE.mdb
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, "")

            Next

            

            ' Trim leading delimiter

            

            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)

            

            ' Concatenate row result to function return value

            

            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem

            .MoveNext

        Loop

        .Close

    End With

    

    ' Trim leading delimiter

    

    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)

    

    GoTo Cleanup



ErrHandler:

    

    ' Error is most likely an invalid database object name, or bad syntax in the Criteria

    

    DConcat = CVErr(Err.Number)

    

Cleanup:

    Set rs = Nothing

    

End Function

Open in new window

0
Comment
Question by:ksilvoso
  • 5
  • 3
  • 2
10 Comments
 
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?

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34206069
Sounds fair to me. ;-)
0
 
LVL 22

Expert Comment

by:8080_Diver
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 ;-).
0
 
LVL 1

Author Comment

by:ksilvoso
ID: 34209681
Sorry.
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
ForEE.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34210708
?

Can you just show us the *exact* output you are looking for? (From James Trimble)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:ksilvoso
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
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 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:

http://allenbrowne.com/ser-06.html
http://technet.microsoft.com/en-us/library/ms174846.aspx
http://msdn.microsoft.com/en-us/library/ms174935.aspx

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.
...etc

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


JeffCoachman
Access-EEQ26636469PedigreeFamily.mdb
0
 
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
...etc

...but these are a separate topics altogether...
;-)








0
 
LVL 1

Author Comment

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

Expert Comment

by:Jeffrey Coachman
ID: 34227114
ksilvoso,

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.

JeffCoachman
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now