Results 1 to 9 of 9
  1. #1
    gedwards913 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    7

    Newbie Import. Update Field of Existing Records from Excel File

    Good Afternoon. I have not used Access since 97, and slowly regaining my confidence in it. I am an advanced Excel user including writing macros, but I want to be able to perform this in Access without having to delete 2000+ records and import them again.



    I have a database of employees, both active and terminated. Somehow their termination dates for the term'ed employees went missing. I have them from an excel file.

    My excel file is simply setup with two columns, one Labeled Name and the other Term Date. These are the same as the field names in my table called Employee. I have tried linking the new data in a table and running an update query, without success. I have dabbled with DLookup in the Update To: portion of this query.

    Is there a simpler way of doing this? I have backed my database up already and open to suggestions. I can provide screenshots if that helps. Thanks!!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,521
    The link and update should work. What happens - error message, wrong results, nothing?

    Post attempted SQL statement.

    Name is a reserved word. Should not use reserved words as names. Also, should not use spaces and special characters/punctuation (underscore is exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gedwards913 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    7
    In one attempt it gave the same value to every record in the field. I am unfamiliar with SQL statements. The file is local on a network share. I will update with underscores instead of Spaces and follow this practice moving forward.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,521
    SQL statements can be seen in SQL View of query builder.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    gedwards913 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    7
    I will post this shortly. re-running the query. Thanks!

  6. #6
    gedwards913 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    7
    Code:
    UPDATE Employees INNER JOIN [Term Date Updates] ON (Employees.Employee = [Term Date Updates].Employee) AND (Employees.[Term Date] = [Term Date Updates].[Term Date]) SET [Term Date Updates].[Term Date] = DLookUp("Term Date","Employees","Term Date =" & [Term Date Updates]![Term Date]);

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,521
    Why do you need DLookup? The INNER JOIN should cause only records that have matching IDs in both datasets to display. Don't you want the value in the Excel file to update the corresponding record in the Access table? If so, then just set the Access field selected on the Field row to UpdateTo the corresponding field from the Excel file.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    gedwards913 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    7
    I was unaware of INNER JOIN. I have been relearning Access, last time I used it was in 97. I will research to see how I can use this. My field I want to use to match is "Employee" and the value that needs to be updated is "Term Date".

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,521
    The query already has the INNER JOIN clause and is matching on the Employee fields.

    Is that a name field? Names make very poor unique identifiers. Is there an employee ID field in each dataset?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 7
    Last Post: 03-22-2015, 02:29 AM
  2. Field F9 error when importing Excel file to existing table
    By dougdrex in forum Import/Export Data
    Replies: 2
    Last Post: 12-26-2014, 01:38 PM
  3. Replies: 5
    Last Post: 02-21-2014, 07:55 PM
  4. Replies: 3
    Last Post: 05-23-2013, 12:17 PM
  5. Replies: 10
    Last Post: 01-29-2013, 08:59 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 - Senior Forums