Concatenating data

[rafeman]

Dear All,

I have a table named SchoolEast which has data in the following format;

SchoolNo Notes Date
1000 Visited 01/09/06
1021 Not interested 10/06/06
1021 Now interested 11/06/06
1000 Spoke to teacher 10/07/06
1000 not interested 10/09/06

I want to write a query that will concatenate the notes per school number seperated by a "-".

E.g the table would now be;

SchoolNo Notes
1000 Not interested - Visited - Spoke to teacher
1021 Now interested - Not interested

The notes would need to be ordered Newest first to oldest.

Is this possible?

Thanks

 

[Remou]

Have a look at:
How to concatenate multiple child records into a single value
faq701-4233

 

[BlueHorizon]

Hi rafeman,

Try entering the following in a new column in your query, having already added the fields that are shown in the concatenation. Make sure the field names are in square brackets and the literal characters (spaces, etc.) are in double quotes.

=[SchoolNo]&" - "&[Notes]

In the column with the [Date] field, add Sort Ascending but you may chose not to Show this field in the Query results.

HTH,


Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[BlueHorizon]

Wait, sorry, update!! I answered to quickly. Enter this instead:

Comments:[SchoolNo]&" - "&[Notes]

"Comments" becomes the name of your new field.

HTH,





Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[lespaul]

BlueHorizon
that will result in a record for each note with the school number in front of it. The OP is asking for ALL the notes on a single record.

Yours:
Comments
1000 - Not interested
1000 - Visited
1000 - Spoke to teacher
1021 - Now interested
1021 - Not interested

what the OP wants:

SchoolNo Notes
1000 Not interested - Visited - Spoke to teacher
1021 Now interested - Not interested

all the Notes in a SINGLE record



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[BlueHorizon]

Excuse me...misunderstood. So what's the answer then lespaul?

Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[Remou]

Why not the FAQ?

 

[rafeman]

Thanks everybody, Remous FAQ link fixed the issue for me.