Solved

Access SQL get concatenation from a field of depending records;

Posted on 2012-12-31
4
311 Views
Last Modified: 2013-01-02
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
Comment
Question by:inversojvo
4 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 80 total points
ID: 38732400
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 190 total points
ID: 38732418
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
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 230 total points
ID: 38732540
0
 

Author Closing Comment

by:inversojvo
ID: 38736160
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

827 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