Concatenating One to many in another field where "many" is variable

[XaRz]

I have a query that shows a One to many relationship:

SELECT CLIE.CLIE_CODI, TLOC.TLOC_DESC, TLOC.TLOC_CODI, CLIETLOC.CLIETLOC_DESC
FROM (CLIETLOC INNER JOIN TLOC ON CLIETLOC.CLIETLOC_TLOC_PK = TLOC.TLOC_PK) INNER JOIN CLIE1 ON CLIETLOC.CLIETLOC_CLIE_PK = CLIE1.CLIE_PK
GROUP BY CLIE1.CLIE_CODI, TLOC.TLOC_DESC, TLOC.TLOC_CODI, CLIETLOC.CLIETLOC_DESC;

This query shows information like:

CLIE_CODI CLIETLOC_DESC TLOC_CODI TLOC_DESC	
...
05074	Comprador       10	Sr. Abel Escudero
05074	Fax             02	972  55.57.24
05074	Telèfon         01	972  554100/554309
05075	Telèfon         01	972  59.70.73
05076	Comprador       10	Sra. Marta
05076	Fax             02      972 20 09 88
05076	Telèfon	        01      972 20 37 00
05077	Comprador       10	Sr. Santaló
05077	Telèfon	        01	609.36.60.19.
05077	Telèfon	        01	972  31.43.44
05077	Telèfon	        01	972  31.61.25
05077	Telèfon	        01	972  31.72.09
05077	Telèfon	        01	972  31.85.51
05077	Telèfon	        01	972  31.86.14
05077	Telèfon	        01	972  60.00.26
05077	Telèfon	        01	972  60.05.00
05077	Telèfon	        01	972  60.07.08
05077	Telèfon	        01	972  60.08.10
05077	Telèfon	        01	972  60.09.26
05077	Telèfon	        01	972  60.12.44
05077	Telèfon	        01	972  60.20.77
05077	Telèfon	        01	972  60.23.30
05077	Telèfon	        01	972  60.24.38
...

Where CLIE_CODI is a customer_ID, CLIETLOC_DESC is the type of data in TLOC_DESC represented as an ID in TLOC_CODI.

I'm working linking tables through ODBC, and I don't want to modify the original tables. Because of this I want to create a Temp table in access containing CLIE_CODI and all information concatenated as:

CLIE_CODI TLOC_CODI(new) INFORMATION(new)

05083 011002 97260.24.38,Sra.Marta,97225.57.24

As I described this, It seems that the FAQ showns the solution of this problem, but in fact my problem is that customers can have multiple and variable information in the field INFORMATION.
My question is: Can I adapt the example in the FAQ with this situation?

Thanks in advance.

 

[dhookom]

Did you try the code offered at faq701-4233? There is a small sample database at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

that demonstrates the function.

Next time, you should show results based on your sample records. It would have been more help full to see the results for CLIE_CODI of "05076" rather than "05083".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]