Newbie Import. Update Field of Existing Records from Excel File

[gedwards913]

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!!

[June7]

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.

[gedwards913]

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.

[June7]

SQL statements can be seen in SQL View of query builder.

[gedwards913]

I will post this shortly. re-running the query. Thanks!

[gedwards913]

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]);

[June7]

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.

[gedwards913]

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".

[June7]

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?