Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Update one table with data from another table without losing data

Results 1 to 8 of 8
Share This Thread →
  1. #1
    tnkrtrn is offline Registered User
    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
    alansidman's Avatar
    alansidman is offline Contributing User
    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    864
    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.

  3. #3
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    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

  4. #4
    tnkrtrn is offline Registered User
    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.

  5. #5
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    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?

  6. #6
    tnkrtrn is offline Registered User
    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.

  7. #7
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    Meant to ask in last post.

    Maybe give me a headstart on solution - show the query SQL statement where you get the values combined.

  8. #8
    tnkrtrn is offline Registered User
    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

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates