Append Query + Concatenate

[benkingery]

I have two tables, one with header records and one with "child" records. For every Header, there is one or more Children records. The header records are orders and the child records are the items on that order. So, for example an order can have more than one item.

I am essentially trying to denormalize these tables onto a "Shipping" table and I want to concatenate the items into one field for any orders that have more than one order.

For example. Order 1 has only 1 Item, so I want this:

ORDER1, ITEM1

Order 2 has 2 items, so I want it like this:

ORDER2, ITEM1 ~ ITEM2

Order 3 has 3 items, so I want it like this:

ORDER3, ITEM1 ~ ITEM2 ~ ITEM3

Does anyone have any suggestions on how to do this?

 

[pbaldy]

Here's one:

http://www.mvps.org/access/modules/mdl0004.htm

 

[benkingery]

I think this is what I need pbaldy. Thanks for pointing it out. So, you actually call the code from a query? I didn't know this was possible. Does the code have to be a public function, then?

 

[pbaldy]

Yes, the code should be a public function in a standard module. You can then call it from a query as demonstrated on the link.