#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2011
    Posts
    4
    Rep Power
    0

    Update one table with data from another table without losing data


    Hi. I'm trying to update data in one table with data from another table without losing data from the first table. The best I can come up with is adding a new row to the first table. Is what I'm trying to do even possible?

    Table1
    ID DATA
    1 data1

    Table2
    ID DATA
    1 data2
    1 data3

    UPDATED TABLE1
    ID DATA
    1 data1,data2,data3

    Thanks Guys
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    I believe that you want to join your two tables in a select query on a common field. Then change the query to a cross tab query to get your results parsed as you demonstrate assuming that data1, data2 and data3 are three different types of data. If this is not the case, then perhaps you need to be more specific about the type of data and why you want them parsed as shown.

    Alan
    Alan Sidman
    Office 2010, 2007, 2002

    If I helped you, then click "give rep" button in the lower left corner.
  4. #3
  5. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,620
    Rep Power
    650
    Need to be more specific about what you want to do. The resulting example you show appears to to put multiple values into the DATA field. How many different values could be in the DATA field of second table?
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2011
    Posts
    4
    Rep Power
    0
    Thanks for your replies guys. The data in the DATA field is just standard text. Right now the data type is text and the field size is 255.

    I'm actually attempting to update the first table with values from the second table without losing anything from the first table. So in the first table id 1 might have a value of 'test' in the data column and in the second table there might be several records with an id of 1 with different values in the data column ('test2','test3' etc). I'd like the first table to be updated with the test2,test etc in the data column without losing the original 'test'.

    I created a relationship on the id column and tried to update the table but I end up creating a new row in table 1 instead of updating the existing row.

    Table1
    ID DATA
    1 data1

    Table2
    ID DATA
    1 data2
    1 data3

    This is what I want
    TABLE1
    ID DATA
    1 data1,data2,data3

    This is what I get
    TABLE1
    ID DATA
    1 data1
    1 data1,data2,data3

    Hopefully this isn't clear as mud but I suspect it might be.
  8. #5
  9. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,620
    Rep Power
    650
    Still need answers.

    There are only 3 possible values from the DATA fields?

    You want the 3 values all in one field separated by commas?
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2011
    Posts
    4
    Rep Power
    0
    There's no limit on the possible values from the DATA fields.

    Yes, I want all the values in one field separated by commas.

    Let me explain. I'm not happy about this but the "boss" wants the data this way because that's the way he wants it. Column 1 is actually email address and column 2 is product code. If you've ordered multiple products you appear in that table multiple times. Instead of creating a quickie search app for him he wants to be able to open a file in excel where the first column is email address and the second column has all the products that email address has ordered separated by a comma. When he needs to look someone up he opens up find and searches for their email address. The second column has the products they ordered separated by a comma.
  12. #7
  13. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,620
    Rep Power
    650
    Meant to ask in last post.

    Maybe give me a headstart on solution - show the query SQL statement where you get the values combined.
  14. #8
  15. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2011
    Posts
    4
    Rep Power
    0
    Figured it out. I'm not sure why the below works but it does. I get an extra comma in the data field but that's easy enough to remove.

    UPDATE table1 INNER JOIN table2 ON table1.id = table2.id
    SET
    table1.data = table1.data &","& table2.data

Similar Threads

  1. Update or coping data into excel sheet.Help needed.
    By cs168 in forum ASP Development
    Replies: 0
    Last Post: April 24th, 2006, 07:09 AM
  2. unable to update the table with selected data
    By netcel123 in forum ASP Development
    Replies: 2
    Last Post: September 15th, 2005, 09:24 AM
  3. Update data from another table
    By Sheikha in forum SQL Development
    Replies: 1
    Last Post: July 11th, 2005, 02:10 AM
  4. Auto Numbering
    By woodstockjs in forum Microsoft Access Help
    Replies: 2
    Last Post: September 28th, 2004, 11:19 AM
  5. Problem with Importing Data to an existing table
    By UniLeo in forum Microsoft Access Help
    Replies: 1
    Last Post: July 5th, 2004, 07:41 AM

IMN logo majestic logo threadwatch logo seochat tools logo