Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2009
    Posts
    48

    Unanswered: Read 4 fields from tbl_1 put in 1 field in tbl_2

    Hi,

    I am using Access 2007. I have table_1 where each record has 4 particular fields (text) that I want to concatenate into a single string (each field separated by a comma & space) and put in a single field in table_2. These tables are in different databases, but for convenience I have put a copy of table_1 in the "receiving" database.

    I would go through table_1 record by record and put certain fields in a corresponding table_2 record. The only tricky part is the 4 table_1 fields I wish to put into a single field in table_2.

    Can someone help me with this?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    214

    ...

    If I understand your question correctly, you want to take 4 fields (for each record) and combine them into 1 field. Your query should look like this.
    [field1] + ", " + [field2] + ", " + [field3] + ", " + [field4]
    substituting for your real field names of course. Then from there you can append it to your other table. If the table is in another database, add the table to this database as a linked table.

    Good luck.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd try the VBA concatenation character & rather than +
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Me too.890
    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
    Jun 2009
    Posts
    48

    Query doesn't work

    Thanks for your help. I tried the following query (and a number of variations of it):

    UPDATE tblworkingstock INNER JOIN [Complete parts list]
    ON (tblworkingstock.Type<>[Complete parts list].Description)
    AND (tblworkingstock.Value1<>[Complete parts list].Description)
    AND (tblworkingstock.Value2<>[Complete parts list].Description)
    SET [Complete parts list].Description = str([tblworkingstock].[Type]) & ", "
    & str([tblworkingstock].[Value1]) & ", " & str([tblworkingstock].[Value1]);

    The result is that only the tblworkingstock Type field is put into the Complete Parts List Description field. I tried this query without using the str function with the same result. I think that I may need to use Visual Basic to concatenate the Type, Value1 and Value2 text and then run a query put the result into the Description field. Unfortunatley, I don't know how to do this.

    Charles

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    I'd try the VBA concatenation character & rather than +
    Surely that depends on the required result, Shirley?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Charles - a few questions.
    Why are you using a theta join (this is when you use inequality operators like <> in the join expression)? They are very rarely required and really look out of place here.
    Why are you updating the column to equal the other three columns, concatenated? Is it so that when you show the description on a screen, it is simply a concatenation of these three columns?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Charles - a few questions.
    Why are you using a theta join (this is when you use inequality operators like <> in the join expression)? They are very rarely required and really look out of place here.
    Why are you updating the column to equal the other three columns, concatenated? Is it so that when you show the description on a screen, it is simply a concatenation of these three columns?

    .....the clue was out there
    each record has 4 particular fields (text) that I want to concatenate into a single string
    before going further i'd want to check your join is valid
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mark are you quoting post 7 and addressing post 6?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2009
    Posts
    48

    Query doesn't work (2)

    I am using that join only because I was trying to follow the Access help on updating a table from another table. The help said to use a join on all of the fields from the first table to the fields of the table to be updated. I did this, but then the resulting SQL required the very field I wanted to update to be equal to the corresponding fields in the table containing the fields I want to concatenate and put into a single field in the other table. So I did the unequal thing. Later, after I posted that the query did not work, I found a field whose content should be the same in both tables and did the join on that field. It is a much shorter and more conventional query, but it still does not work as I want it to.

    I found a writeup where the author was updating a single field using a concatenation of several fields from different records. The whole thing is written in Visual Basic and uses DAO recordsets and database which are still a mystery to me. I have been unable to adapt his code for my purpose.
    (Concatenate Records Text Field)

    So I am still in need of help on this.

    Thanks,

    Charles

Posting Permissions

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