Results 1 to 6 of 6

Append Data to a table from another table.

This is a discussion on Append Data to a table from another table. within the Microsoft Access forums, part of the Question Forums category; Hello... I have two tables that looks (simplified) like this... Table 1: UniqueID - Name - City - State - ...

  1. #1
    New Member
    Join Date
    Mar 2006
    Location
    Colorado
    Posts
    43

    Default Append Data to a table from another table.

    Hello...

    I have two tables that looks (simplified) like this...

    Table 1:

    UniqueID - Name - City - State - Zip - Color
    1234567 - Dell - small - CA - 80010 - BLUE
    7654321 - MAC - Large - OR - 85214 - RED

    Table 2:

    UniqueID - Name - Occup - Title - State - Color
    1234567 - Dell - Geniology - Analyst - CA - ??????
    7654321 - MAC - healthcare - Athlete - OR - ??????

    PROBLEM

    I need to get the COLORS to table 2... (using the unique ID's as a reference between both tables).

    Any help would be greatly appreciated.

    THANKS

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,336

    Default

    The first question you need to ask, is do you really need to do this? Usually, if it can be done using a query, there is no reason to update the tables. As matter as fact, doing so could compromise the integrity of your database (what if after you update Table 2, a value in Table 1 changes - now the tables have discrepancies).

    Usually, you only need to update the other table (instead of using a query) if you are trying to capture & store history (and not current values).

    If you do need to update the table, take a look in Access' built-in help for Action Queries, specifically the "Update Query". This should give you details and examples on how to do it.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Mar 2006
    Location
    Colorado
    Posts
    43

    Default ?????? Now I don't know what to do...

    I just need table two to capture table ones color field... you could use the unique ID to pull those over.

    I don't know what to use now. I tried an "append query" and now I have 200,000 extra rows that I don't have any idea where they came from.

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,336

    Default

    An "Append Query" adds new records. An "Update Query" updates fields in existing records. So you would want to use and "Update Query".

    But I go back to my original point. Why do you need it in the table 2 (because you can represent the exact same information in a query without having to update anything)? You can use query for just about anything that you use tables for, and unlike tables, queries are dynamic.

    You can link Table 1 and Table 2 in a query, and return all exisiting fields from Table 2 in your query along with the color from Table 1.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Mar 2006
    Location
    Colorado
    Posts
    43

    Default I tried a query

    A query would be ideal...

    I created a query...
    brought in both tables
    matched the unique ID's
    Selected all the columns in table 2
    Selected Color from table one.
    and RAN it

    I got back much more rows than I had in table two. So I had something like 150k rows in table two and the query returned 210k... I don't get it.

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,336

    Default

    Sounds like you have a linking problem.

    1. Make sure that your unique IDs are truly unique within each table (i.e. within each table, each ID only appears on one record). You mentioned you did an Append Query that added a bunch of rows. Did you clean these back out?

    2. Make sure your two tables are linked by the unique ID and nothing else. If you do not link the two tables, your total number of rows will be the product of the number of rows in each table.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com