Access SQL get concatenation from a field of depending records;
Posted on 2012-12-31
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):
So I need a query, that gives me 2 fields back:
(all specContractAddress values to the bundleContractID above).
Example ('--' is a field separator):
bundleContractID -- bundleContractAddress
1 -- "Adr1"
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!
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.