Feb 25 2009, 09:18 AM
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:
Could you rewrite your query to show:
Brian.......Fido, Goldy, Silly
without resorting to VBA?
Feb 25 2009, 09:45 AM
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.
Feb 25 2009, 09:46 AM
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.
Feb 25 2009, 10:01 AM
Try a cross tab query.
Feb 25 2009, 10:38 AM
Jun 3 2009, 10:23 PM
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here