Solved

Access SQL get concatenation from a field of depending records;

Posted on 2012-12-31
4
304 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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Closing Comment

by:inversojvo
Comment Utility
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

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

Join & Write a Comment

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…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

763 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

7 Experts available now in Live!

Get 1:1 Help Now