Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Access_throwaway is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    13

    Export Filtered table to Excel, Edit and Re-Import Changes to Access Table

    Hello,

    Forgive me as I am just at a beginner level in Access. I have a db with multiple tables. I am working with just one at the moment. I have filtered the table (table1) and exported the contents to Excel for editing. Within Excel, I am moving the data into different columns within the record. I would then like to re-import the data back into Access to update only those records in the table (table1) that had data changed.



    Is anyone available to help explain how to accomplish this or provide a link?

    Thanks for your assistance!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    This sounds like a terrible idea. You should make ALL your edits in Access, THEN export data to excel.

  3. #3
    Join Date
    Dec 2016
    Posts
    13
    I understand, but there is large amounts of data that needs to be looked at individually, per record. I cannot have a query move the data because not all meets the criteria. Since some of the data is in groups, it would be easier for me to copy and paste a set of data in Excel, than Access. The problem is some of the data was previously entered into the wrong fields. Since some of the data was entered it correctly, I can't just move it all.

  4. #4
    Join Date
    Dec 2016
    Posts
    13
    My table has a LastName field and CompanyName field.
    In many cases, a company name was entered into the LastName field. So I want to move that data into the CompanyName field.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you can create a query that shows only the records that need to be modified, you can edit the data in that query's datasheet. To male it even easier, after creating the query, highlight it in the navigation pane, in the ribbon click 'Create > Form', and you'll have an instant form to handle the editing.

  6. #6
    Join Date
    Dec 2016
    Posts
    13
    Quote Originally Posted by davegri View Post
    If you can create a query that shows only the records that need to be modified, you can edit the data in that query's datasheet. To male it even easier, after creating the query, highlight it in the navigation pane, in the ribbon click 'Create > Form', and you'll have an instant form to handle the editing.
    Unfortunately, I can't make a query to show the records to change..it will need real eyes on. For example, ACME may be in LastName, whereas it needs to be moved to CompanyName. But SMITH may be in LastName field, where it belongs. So, I can't use a query based on the number of words in the field.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Based on your post #4, even easier.
    If you can create a query that displays only the records to be modified, save that query as qBadData, the create a new query that updates that query:
    Code:
    UPDATE qBadData SET qBadData.CompanyName = [LastName];
    This leave the modified records with the same data in both fields.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    From Post #1 you said:
    I have filtered the table (table1) and exported the contents to Excel for editing.
    Doesn't this mean that you have separated out the bad records?

  9. #9
    Join Date
    Dec 2016
    Posts
    13
    Quote Originally Posted by davegri View Post
    From Post #1 you said:


    Doesn't this mean that you have separated out the bad records?
    Sorry, not exactly...I should have explained better. I've filtered the table to a list of potential records that need to be edited. So I filtered a list of 250k total records down to 5k that potentially need to be updated. So I took that list of 5k results into Excel and was doing the edits there...

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    What format is the filter list in? Is it a query? If so, post #5 still applies. Just update the records that need changing, leaving the OK ones alone.
    This assumes that the query is sourced on table1.
    Last edited by davegri; 02-19-2018 at 10:46 AM. Reason: clarif

  11. #11
    Join Date
    Dec 2016
    Posts
    13
    No, filtered list is not a query. I just applied a filter to the column headers and then exported the resulting data to Excel

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you create a query based on the entire table, you can set criteria in the query design grid to select the appropriate records. When you get the query right, you can use the technique in post #5 to create an editing form. Only the records in the query will display in the form.

  13. #13
    Join Date
    Dec 2016
    Posts
    13
    OK thanks @davegri

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you post a db with table1 containing a sample of the data you want to clean, and tell me the decision process for selecting bad records (your filter criteria), I think I can return the db with the query and the form.

  15. #15
    Join Date
    Dec 2016
    Posts
    13
    Thanks for your help Dave, got it to work! Thanks again

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 06-20-2017, 12:27 PM
  2. Replies: 1
    Last Post: 10-26-2016, 06:38 AM
  3. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  4. Replies: 4
    Last Post: 03-19-2015, 06:14 PM
  5. Replies: 4
    Last Post: 12-17-2012, 01:21 PM

Tags for this Thread

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