Rolling up child records into one field. 1

[travisbrown]

How would one roll up multiple records in a child table to one field in a join expression between a parent and child?

e.g.

Select a.name, b.note FROM a LEFT OUTER JOIN b ON a.id = b.fid

Returns, as it should

name1 | note1
name1 | note2
name1 | note3

I want...

name1 | note1,note2,note3

In SQL Server I'd use a user-defined function. Not sure how to do it in Access.


Travis
..with the strength of a grizzly, the reflexes of a puma, and the wisdom of a man.

 

[genomon]

Only small clue I can offer: look up "crosstab query" in Access help - seems like I've seen this stuff on pivot tables before. Sorry I can't be more specific....
Happy Thanksgiving!

The Microsoft mascot is a butterfly.
A butterfly is a bug.
Think about it....

 

[travisbrown]

I don't think pivots or crosstabs are the thing I'm looking for. This isn't actually aggregation; it's collation.

 

[dhookom]

There is a generic concatenate function and sample usage at

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

The same function is probably in the FAQs (with others) in this forum.

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]

 

[PHV]

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[travisbrown]

Thanks guys.

Oky...big pain in the ass....this is an .mde so I can't save the module. I was originally writing this query to autoupdate an excel sheet. Rather than creating another db and a passthrough, is there a way to do it in Excel, or a long convoluted way directly in sql?

 

[achillese]

If my understanding is correct, the way to do it is by using concatenation:

in EXCEL do it this way:
---------------------------
A B C D E
1 I AM HAPPY =B1&" "&C1&" "&D1
2 NO I AM NOT =B2&"-"&C2&"-"&D2

The result in row 1 will look like this in cell E1:
I AM HAPPY


The result in row will look like this in cell E1:
NO-I AM-NOT

Anything enclosed in quotes is considered a string or text.


IN ACCESS DO IT THIS WAY:
-----------------------
FIELD1
FIELD2
FIELD3 = FIELD1&" "&FIELD2

YOU DON'T ENCLOSE FIELDS IN QUOTES.

I hope this helps.

 

[travisbrown]

thanks achillese, but I need to concatenate rows, not columns.

 

[dhookom]

You can use the pure sql method. Try open Northwind and modify the Category table adding a memo field "AllProducts". Then create an update query with this SQL view:

UPDATE Categories 
   INNER JOIN Products 
   ON Categories.CategoryID = Products.CategoryID 
SET Categories.AllProducts = [AllProducts] & ", " & [ProductName];

You only need to them remove the first two characters from the AllProducts field to get a comma delimited list of products in each category.

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]