I have a feeling the answer is no, but I thought I'd ask anyway.

In strict SQL (because this is actually for a pass-through query), can you concatenate subrecords instead of repeating them?

E.g. if you had a table called tblOwners and a subtable called tblPets, if I have 3 pets, it might look like this:

Owner.....Pet
Brian.......Fido
Brian.......Goldy
Brian.......Silly

Could you rewrite your query to show:
Owner.....Pet
Brian.......Fido, Goldy, Silly

without resorting to VBA?

Thanks!

Without resorting to VBA? Not really.

If you knew the maximum possible number of subrecords, you could join the table to itself multiple times, extracting only one subrecord for each instance and concatenate that way, but it would be slow and messy.

Thanks Doug, that's what I figured. I already have a VBA function that does this, was hoping to speed it up a touch, but it'll have to do.

Try a cross tab query.

Hi
Check out: How to Concatenate Data from the 'Many' Side of a Relationship
HTH's

Hi,

I too have a similar request. So is it possible to post your solutions? I am using a query to poulate a report and wish to concatendate licence information into one line like the original example post.

Any help appreciated.