How to do vertical concatenation

  • Thread starter Thread starter doyle60
  • Start date Start date
D

doyle60

If I have a query that returns two fields, let's say, MyID and
MyComment (which can be null) and I have data that looks something
like this:

MyID--MyComment
1--------Must do
1--------
1--------Not Entered
1--------Give to sally
1
2--------Not ready.
2--------Okay to go
3--------
3--------
4--------Rufus has.

How do I get it to return one line per MyID with a concatenation of
the vertical MyComment data?:

MyID
1---------Must do, Not Entered, Give to sally
2---------Not ready., Okay to go
3---------
4---------Rufus has

I suppose it is a function. Thanks,

Matt
 
Thanks. I loaded in the function but am unsure how to use it in the
query. I get a wrong number of arguments.

So let's say I have this:

Table: OrderDetails
Field to Group On: PO
Field to Concatenate: Style

and this sample data:

PO---------Style
11000-----St332
11000-----St3388
11000-----St3388XS
11007-----GG88
23200-----GG88
23200-----TT0900

Which should return:

PO---------Styles
11000-----St332; St3388; St3388XS
11007-----GG88
23200-----GG88; TT0900

What should I do in my query? If I understand his directions
correctly, I should do this:

PO
Styles: fConcatFld("OrderDetails","PO","Style","String","????").

I'm not quite sure what he means by the last bit, which he calls
"vForFldVal As Variant" and has "Owner" in the sample.

Thanks,

Matt
 
That field's looking for a specific value of PO.

The SQL of your query would be something like:

SELECT DISTINCT PO,
fConcatFld("OrderDetails","PO","Style","String", [PO]) AS Styles
FROM OrderDetails

although if your table is large, it would be better to use:

SELECT PO,
fConcatFld("OrderDetails","PO","Style","String", [PO]) AS Styles
FROM
(SELECT DISTINCT PO
FROM OrderDetails) AS Sub
 
Thanks. The first one works well. But the second one gives me a
syntax error (and I think it best to use this one). I put a semi after
it and it still didn't work. I tried to reduce the number of lines
and it still didn't work. It gives me an error on the first "FROM".
Any suggestions?

Thanks,

Matt
 
That's odd. I'll admit I didn't have Access handy to test the solution when
I posted it, but when I copied that SQL into a sample database just now, it
worked flawlessly.

Copy your SQL into a reply, in case you made an error.
 
It's the same as you have it:

SELECT PO,
fConcatFld("OrderDetails","PO","Style","String", [PO]) AS Styles
FROM
(SELECT DISTINCT PO
FROM OrderDetails) AS Sub;

But I put a semi on the end. It says "Syntax error in FROM clause"
and highlights the second "SELECT".

I have Access 97 by the way.

Thanks,

Matt
 
Back
Top