I have a database that imports employees from an Excel file.
I have an append query that adds new employees and an update query that updates information on the current employee (i.e. Name Changes, Address, etc.).
I have a field on a table called Departed which I have to update manually between yes and no if an employee leaves.
I am trying to find a way that if the employee is not in Excel, then change their Departed record from no to yes in Access.
Thanks in advance.
Assuming you have imported/linked the Excel dataset as a table in your Access database, then you can update the Departed field using an update query with a left join from your database table to your Excel dataset, testing whether the records on the right of this join are Null (i.e. don't exist in the Excel dataset).
Such a query might look like this:
update Employees left join ExcelEmployees on Employees.ID = ExcelEmployees.ID
set Employees.Departed = -1
where ExcelEmployees.ID is null
This assumes that your database table is called Employees, your imported/linked Excel dataset is called ExcelEmployees and that there is a primary key field called ID which may be used to join the two.
