Solved

Access SQL get concatenation from a field of depending records;

Posted on 2012-12-31
4
309 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 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

861 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

30 Experts available now in Live!

Get 1:1 Help Now