asked on

Concatenating multiple rows into 1 column in MS Access Query

I would like to be able to concatenate multiple rows into one column within a query if possible not using VB. I have a unique number for each student SCN and a list of subjects they are currently failing. If there are more than one I would like them in the one field. I can easily get them into multiple columns using a crosstab query but I need them in one field if possible. Like the table below. Is this possible ?

Tricia

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Tricia McKeating

ASKER

Hi Jim,

OMG you have changed my life !! That's a brilliant function and even a non-VBA user like me could follow the instructions.

Thanks so much for this I have a ton of places it could be used.

Jim Dettman (EE MVE)

No problem. With Access being as mature as it is, there virtually nothing anymore than someone hasn't already done at one point or another, and usually offer an article and/or code to document it.

So mainly at this point, it's just knowing where to look<g>

Jim.

Gustav Brock

There is a newer and much faster method:
Join (concat) values from one field from a table or query

Jim Dettman (EE MVE)

@gustav,

Have you ever had issues with memory pressure? I would think caching results for a large table would give you some memory pressure. Probably not with 64 bit, but with 32 bit, it might give you a problem.

Jim.

Gustav Brock

Not that I've seen. But I don't use 32-bit anymore.