Concatenation I think?

[Oscar]

I have a table which is grouped by hole ID and has target From Depths along with other information. I would like to report by Hole ID the from depths in a concatenated view.
Can I do this, and if so how?

eg
Hole ID Target From Other Info......
WSGC11 2 ...............
WSGC11 10 ...............
WSGC11 50 ...............

Desired report;

WSGC11 2, 10, 50

 

[Mile-O]

Add a calculated field into your query:

NewField: [Hole ID] & " " & [Target] & ", " & [From] & ", " & [Other Info]

 

[Oscar]

That works fine if the info I'm trying to concatenate was along one line for the hole ID. However, this is not the case and is what is causing me dramas. I have a Hole WSGC1010 which has several From Depths down a table

eg
Hole ID, From_Depth
WSGC1010, 2
WSGC1010, 10
WSGC1010, 11

and I want to concatenate so that it looks like this in a query;
Hole ID, From_Depth (as separate columns)
WSGC1010, 2,10,11

Your help would be much appreciated.

 

[Pat Hartman]

Try this article from the Microsoft knowledgebase.

Q141624 - ACC How to Concatenate a List of Items from a Many-Side Table

 

[Oscar]

Thanks heaps, works a gem.