• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Access SQL get concatenation from a field of depending records;

Hi experts,

I have a problem and can't even find a good search words to make a descent search. So I'll describe a context better hoping, that you can help me.

I've 2 tables: tbl1 and tbl2 with 1 to N dependency between them (1 bundle contract can contain N specific contracts):

tbl1:

bundleContractID
bundleContractAddress

tbl2:

specContractID
bundleContractID
specContractAddress

So I need a query, that gives me 2 fields back:

bundleContractID
(all specContractAddress values to the bundleContractID above).

Example ('--' is a field separator):

tbl1

bundleContractID -- bundleContractAddress
1 -- "Adr1"

tbl 2
specContractID -- bundleContractID -- specContractAddress
1 -- 1 -- "Adr2"
2 -- 1 -- "Adr3"
...
N -- 1 -- "AdrN"

N may vary from bundleContract to bundleContract.. And I don't know in general case,
if there is only 1 Adress or many of them, that fit to each bundleContract.

Query expected results:

bundleContractID -- specAdrSummary
1 -- "Adr2, Adr3, .. AdrN"

So I need to have a kind of a loop on depending records and concatenate the values in a specific field in one SQL expression.

How can it be done? Do you have a tip?

Thanx in advance!

Yuriy

P.S. If you have the solution in any other SQL-Language (DB2-SQL, MySQL, etc.), it's OK, I'll map it then somehow on Acccess SQL.
0
inversojvo
Asked:
inversojvo
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in mysql, it exists: group_concat.
here a thread for a alternative for ms access:
http://stackoverflow.com/questions/2852892/is-there-a-group-concat-function-in-ms-access
0
 
Dale FyeCommented:
I use a function for this:

To use this, your query would read something like:
SELECT Table1.bundleContractID,
     fnConcat("specContractAddress", "table2",,,"[bundleContractID] = " & Table1.bundleContractID)
FROM Table1

Open in new window

As you can see, the function provides several options including:
Delimeter - character or characters to separate the various elements
Wrapper - character to put before and after each element of the result set.  I use this to wrap strings in quotes or single quotes
Criteria - Determines which records to combine
UniqueValues - If true, the result set will exclude duplicates
Public Function fnConcat(FieldName As String, TableName As String, _
                         Optional Delimeter As String = ",", _
                         Optional Wrapper As String = "", _
                         Optional Criteria As Variant = Null, _
                         Optional UniqueValues As Boolean = False) As String

    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim varConcat As Variant
    
    strSQL = "SELECT " & IIf(UniqueValues, "DISTINCT", "") & " [" & FieldName & "] " _
           & "FROM [" & TableName & "] " _
           & ("WHERE " + Criteria)
    Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
    
    varConcat = Null
    While Not rs.EOF
        If Not IsNullOrBlank(rs(0)) Then
            varConcat = (varConcat + Delimeter) & (Wrapper + rs(0) + Wrapper)
        End If
        rs.MoveNext
    Wend

ConcatExit:
    fnConcat = Nz(varConcat, "")
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Exit Function
    
ConcatError:
    Call MsgBox("Something failed in the concatenation function")
    Resume ConcatExit
        
End Function

Open in new window

0
 
inversojvoAuthor Commented:
Thank you very much, guys, that was what I've looked for. <br /><br />The first answer was a pointing a direction, the 2nd and 3rd have had the necessary code, the 3rd one was the most complete, so I hope you're OK with the points distribution.
0
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now