Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    Hamburg, Germany
    Posts
    14

    Unanswered: How to select multipe rows into one field

    Hi all,

    I've got a query that returns several strings - each in a separate row. What I want to do now is join them all into one single field of another query e.g. separated with colons.

    How can I do that?

    Thanks for your help,
    Ulf Moehring

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Smile Re: How to select multipe rows into one field

    Originally posted by ulf.moehring
    Hi all,

    I've got a query that returns several strings - each in a separate row. What I want to do now is join them all into one single field of another query e.g. separated with colons.

    How can I do that?

    Thanks for your help,
    Ulf Moehring

    If there is a maximum number of rows, you can expect, you can solve this by defining views, each of them containing one row. These views can be joined in order to combine your data.

    If you don't understand my answer, please provide me with some more structure information, so I can illustrate my suggestion.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Nov 2002
    Location
    Hamburg, Germany
    Posts
    14
    Views in Access? Never heard of that! Anyway the result of my first query looks like this:


    | Comment |
    User lost his password
    User password locked
    ...


    I want to join that information into my second query to make it look like this:


    | Category |
    Account

    | Comment |
    User lost his password, User password locked, ...


    ...with category and comment being the field names. Hope I made myself a bit more clear this time.

    Thanks for your help,
    Ulf Moehring

  4. #4
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    you'll have to open recordset, create empty string, go through recordset and concatenate all records into one string....

    then you can pass new string to the second query....

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by Strucnjak
    you'll have to open recordset, create empty string, go through recordset and concatenate all records into one string....

    then you can pass new string to the second query....

    Strucnjak: You are thinking on a solution using a VBA module and writing a function, right? I guess that Ulf is rather looking for a query-based solution.

    Ulf: Views in Access are queries returning data like SELECT and UNION queries.

    I described a solution just in the SQL Server section:

    http://dbforums.com/showthread.php?p...99#post3235299

    Let me know whether this works for you.
    Prost!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •