concatenate

[lestats]

Hi,

Not sure I have to pass by the concatenate build-in fonction. I have a table such as :

col 1 col 2 (is a memo field)
1 a
1 b
1 c
2 d
2 e


I want the output to be like this with new columns :

col 1 col2 col 3 col 4
1 a b c
2 d e

Is there a way to do this by a query ?

Thanks

 

[pr2-eugin]

Two ways ! The first one is Cross tab Query. The next one is concatenate values from similar records.

 

[lestats]

I think the link you provided, mentions that is limited to 255 characters and one of my field is a memo. I cant let access trunck this field. I will look in the cross tab query first.

 

[pr2-eugin]

I am not sure how it is going to work with CrossTab Query. Give it a try and let me know how it goes.

 

[lestats]

I found a way to make it works with Cross Tab. However Access trunks the memo field. Is there a walkaround I can do to have the entire information in my cross tab.

here the SQL :

TRANSFORM First(ROD.ROD) AS FirstOfROD
SELECT ROD.id
FROM ROD
GROUP BY ROD.id
PIVOT [ROD]![ROD number];

ROD.ROD is my memo field.

 

[pr2-eugin]

See if Allen Browne's explanation of truncation of Memo fields is of any help.