• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Retrieving multiple records from a dataset and listing the results on one line in a msquery statement

I have a table that has multiple  event records for one member. Is there a way in a MsQuery statement to loop through 10 times to get the next record in a set. A simplistic look at what I want to see is:
    Member1, Event1,Event2,Event3,Event4,Event5,Event6,Event7,Event8,Event8,Event9
    instead of:
    Member1 Event1
    Member1 Event2
    Member1 Event3
    Member1 Event4
    Member1 Event5
    Member1 Event6
    Member1 Event7
    Member1 Event8
    Member1 Event9
    Member1 Event10
2 Solutions
if you are using mysql, you have the group_concat operator, but I think you are using MS Query and MSSQL as a back end. In that case, you have to make a subquery and concatenate all the results. See this link:

I'm not sure about the query, but if your results are appearing in excel as listed above, you can copy it, selct paste special and select transpose. This will give you the result you're looking for. You can use a macro like this:

Sub Transpose()
Range("A1:B10").Select '<Select the copy range here
    Sheets("TragetSheetName").Select 'Set destination range here
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now