Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2007
    Posts
    5

    Combine many columns/fields into 1 column

    Hi,
    This looks like a uphill task for me. may be I could get some help in resolving or finding a work around.

    The task is :

    I have a table - Table with 100+ columns.
    column1 column2 column3 ............... column100
    A B C ............... R

    I have to create 1 record by concatenating all the columns togather.
    This merged record needs to be written to a file.

    O/P

    A B C ............... R

    VBA is one option or write a select query. But it will very tiresome and time consuming to write up all the columns.
    I will need to write a query or VBA because, I will taking a field from the user based on which the data will be pulled and written to the output file.
    Is there some macro/module option to handle this.

    I need all the values in the columns and columns should be in the same sequence and with their sizes as they are in the table.

    Can anyone suggest any easy way to merge the columns into 1.
    I cannot use 3rd party s/w.

    Thanks in advance.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,925
    Create a "Combined" field in the table and then in an update query, update it with the fields you want (ie: [FirstName] & " " & [LastName]).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2007
    Posts
    5
    Hi,
    Thanks for the reply.
    But writing it in the form of ie: [FirstName] & " " & [LastName], for 100+ fields seems very tedious.

    Any other suggestions.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Meh. So it will take you 5 or 10 minutes. Big deal. Tediousness is often part of coding!

    It will be long done by the time any easier solution is figured out.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    As Trekker said, just writing the "tedious" query may take a few minutes, but it'll probably be the quickest method.

    The only other way around it that I see would be to loop through the Table's Field Names and create the query with code and doing a debug.print with a ctrl+c. But I'm quite sure that would be a whole lot more time consuming with all the coding and debugging you'd have to do than just creating the query by hand once.
    Me.Geek = True

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,523
    actually it isn't that tedious, unless you have defines columns with spaces in between

    open the query designer
    drag every column into the boxes (NOT the *, EVERY column)
    save the query
    switch to SQL view

    replace the commas in the query with amerpsands
    eg
    Code:
    select my, comma, separated, list from mytable
    becomes

    Code:
    select my & comma & separated & list from mytable
    then add a place to store the resultant mishmash
    Code:
    select my & comma & seperated & list as myconcatenatedcolumn from mytable

    Code:
    select my & comma & seperated & list & [my space separated column] as myconcatenatedcolumn from mytable
    you could then modify that query so it beomes in insert query into another table, and export that table to a text file

    otherwise you coudl do the whole thing using VBA to write to a file. Persoanlly I'd use a SQL query, but each to their own
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Are the values of all these columns the same width?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Aug 2007
    Posts
    5
    Thank you for the replies.
    I have started working in the direction to use the wizard and drag-drop the fields.

    pootle_flump : No, the fields are of different size - right from text size 1 to memo field.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by Akanchu
    pootle_flump : No, the fields are of different size - right from text size 1 to memo field.
    In that case, how are you ever going to read this file? You either need to delimit values, or use fixed width, to allow the file to be parsed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Aug 2007
    Posts
    5
    I will need fixed size file. I will be ftping the file to m/f for further processing.

  11. #11
    Join Date
    Apr 2009
    Posts
    14
    Akanchu,

    There is a simple solution to your problem. Export the whole table to a text file. You can use what you like as delimiters for the fields but make sure it’s something that you can recognize if you need to.

    Next –import the text file into a new table as a single text column.

    And there you have it.

    MIKE

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by Akanchu
    I will need fixed size file.
    What is fixed file size? Do you mean fixed width?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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