Results 1 to 8 of 8
  1. #1
    AccessNub is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    7

    Adding rows from a similar table to a pre-existing table

    Hi all. I'm new to Access and am already being thrown some things to do in Access at my job. Some of them I know would be pretty easy to do in Excel, but I'd like to learn how to do them quickly and effectively in Access. One of those things seems like it would be an incredibly simple, intuitive operation, but it's not, at least not to me.

    Scenario:

    Say I have some data that really should only be on one table, but it's currently on two tables in Access: "List Main" and "List September Adds." They both have the following fields: "Full Name" "Email" "Address" "City" "State" "ZIP" "Phone."



    The first table is my main list of contacts. The second contains new info, consisting of 1. a few new contacts, and 2. updated info for a few of the contacts already in table "List Main."

    I would simply like to put all the new contacts from "List September Adds" into my "List Main" table, and I'd also like to fill in a few missing e-mails in "List Main" with newly gathered e-mails for those contacts, info that is in my table "List September Adds."

    Hopefully I explained that relatively clearly. Here's what I'm looking for. I have a feeling there is a very easy way to do this; I'm sure there is built-in functionality to do something as basic as essentially turning two pages of the same spreadsheet into one. In fact, I know I could import data from an Excel file and have it "append" to a preexisting table if the fields are all the same. However, I'd like to know how to do it when the tables are already in Access (without having to learn any SQL, mostly because I'm fairly certain I don't need to know SQL to do something like this). I've heard of Append and Update Queries and given their names, it sounds like they'd be helpful, maybe with use of "Totals" and "Group By" to get rid of duplicates; however, I can't seem to get any of this to work right. Mostly, when I think I am doing an Append correctly, it doesn't add new data at the bottom of a table; it just wipes out all the data that was there, and replaces it with data from the source. For instance, when I've been running an Append Query to get my "List September Adds" rows into my "Main List" table, what I end up with is only the "List September Adds" rows, and all the "Main List" rows gone. Am I on the right track or is there a tool I'm missing? Like I said, I'm fairly certain there is a simple way to do this sans SQL knoweedge.

  2. #2
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Import your excel as a table then append the two tables into another new table?

  3. #3
    AccessNub is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    7
    Quote Originally Posted by Ruegen View Post
    Import your excel as a table then append the two tables into another new table?
    I have my two tables (the main one and the one with info I'd like to add to the main one) already in Access. When you say append both of them into another new table, how would I go about doing that? Like I said, whenever I run the append query, it seems to wipe out data in whatever table I'm appending TO and replace it with the data from the table I'm appending from.

    Also, is there a place here to attach two small sample tables (say, three fields and three rows each)?

  4. #4
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by AccessNub View Post
    I have my two tables (the main one and the one with info I'd like to add to the main one) already in Access. When you say append both of them into another new table, how would I go about doing that? Like I said, whenever I run the append query, it seems to wipe out data in whatever table I'm appending TO and replace it with the data from the table I'm appending from.

    Also, is there a place here to attach two small sample tables (say, three fields and three rows each)?
    You should be able to simply append those fields of the imported table into the fields of the table you want selecting only those fields.

    Use dropbox, get an account and then post the link.

  5. #5
    AccessNub is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    7
    Quote Originally Posted by Ruegen View Post
    You should be able to simply append those fields of the imported table into the fields of the table you want selecting only those fields.

    Use dropbox, get an account and then post the link.
    Is it possible that if I have "Main Table" open when I do the Append action, I need to close and REOPEN it after the append is complete? I don't see any changes when I leave it open, and when I click "View" on the query, it only shows me the rows to be appended... But I just closed out "Main Table" and reopened it, and my rows had been appended... Does that sound like a reasonable thing to happen, that I just kept missing?

    EDIT: It seems like the "Refresh" button does the same thing. So I suppose I have to refresh a table after it's been appended for the changes to show up? I didn't see any instructions to this effect when I was looking for how to do this, but maybe that's the case?

  6. #6
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by AccessNub View Post
    Is it possible that if I have "Main Table" open when I do the Append action, I need to close and REOPEN it after the append is complete? I don't see any changes when I leave it open, and when I click "View" on the query, it only shows me the rows to be appended... But I just closed out "Main Table" and reopened it, and my rows had been appended... Does that sound like a reasonable thing to happen, that I just kept missing?

    EDIT: It seems like the "Refresh" button does the same thing. So I suppose I have to refresh a table after it's been appended for the changes to show up? I didn't see any instructions to this effect when I was looking for how to do this, but maybe that's the case?
    The general idea is that you close both the tables, create a fresh new query and drag in the table with the data you want to shift over, click to append so that the query is an append query, select the table that you want to append the data to and then choose what field goes to where. So if it is NameField that you want the data to append to you find the matching field in the table.

    So if you have a column of dates then you will need to append that to a column of dates in the other table.

    Once you have the rows set up you click "run" the big red exclamation mark. After that you do a little prayer in case you didn't append it correctly (and end up writing over data and losing what you had). all the rows form the old column should now be appended to the table that you matched it to.

  7. #7
    AccessNub is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    7
    Quote Originally Posted by Ruegen View Post
    The general idea is that you close both the tables, create a fresh new query and drag in the table with the data you want to shift over, click to append so that the query is an append query, select the table that you want to append the data to and then choose what field goes to where. So if it is NameField that you want the data to append to you find the matching field in the table.

    So if you have a column of dates then you will need to append that to a column of dates in the other table.

    Once you have the rows set up you click "run" the big red exclamation mark. After that you do a little prayer in case you didn't append it correctly (and end up writing over data and losing what you had). all the rows form the old column should now be appended to the table that you matched it to.
    Thanks for writing that out, I know this is an incredibly simple operation. I think the reason it didn't seem to work for so long was simply that I had had the destination table open, and the append action only shows up after you reopen/refresh...

  8. #8
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by AccessNub View Post
    Thanks for writing that out, I know this is an incredibly simple operation. I think the reason it didn't seem to work for so long was simply that I had had the destination table open, and the append action only shows up after you reopen/refresh...
    You have different views, one view is for viewing the fields and the data contained within (before you actually append) and then design to choose what fields append to where. After you click run you won't see anything unless there is a large amount of records (in which case you will see a progress bar much like the database I am currently working on :-| )

    You can open the table to see the records of course and hopefully if done right they match up. Linking tables in a query helps keep things matched by making sure they're related correctly. But since you are bringing in an exel, you're just basically pasting in with corresponding number of records.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding data from linked table to existing table???
    By claysea in forum Import/Export Data
    Replies: 3
    Last Post: 02-21-2013, 12:23 PM
  2. Replies: 11
    Last Post: 07-24-2012, 07:50 PM
  3. Replies: 1
    Last Post: 01-31-2012, 11:54 AM
  4. Adding records to existing table
    By Mclaren in forum Programming
    Replies: 5
    Last Post: 05-18-2011, 12:44 PM
  5. Replies: 7
    Last Post: 04-15-2011, 08:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums