Question Removing data from table based on Excel spreadsheet

[Passa]

Hi folks,

I'm trying to find an easy way to remove clients from a consumer database based on a list of people who are no longer clients in an Excel spreadsheet.

The database itself is accessed through ODBC links in an Access file - I need a way for Access to read this spreadsheet and remove all clients who have those details.

The ODBC table has an ID column, then many other fields. The spreadsheet has matching Firstname, Lastname, DOB, Postcode and Gender columns. I need Access to remove anyone from the ODBC table who have all of those same columns matching up in the ODBC table (hence are the same person and are no longer clients).

I hope I haven't made this more confusing to understand. I have tried toying with a delete query after importing the Excel spreadsheet into the Access file but could not figure out how to get it to work.

Is it possible someone could point me in the right direction?

 

[Galaxiom]

Import the spreadsheet into Access.

Consider there may be records in other tables related to the clients you want to remove. There may be issue with referential integrity if the clients are removed.

I would recommend not deleting the records from the table but adding a Y/N field to indicate Deleted. This way they can be easily restored in case of error and any referential integrity won't be damaged. Change the Access queries to ignore those with Deleted checked.

Make an update query. Join the fields in the imported spreadsheet to the matching fields in the table. Set the UpdateTo cell to update the Deleted field.

 

[Passa]

Thankyou for the reply. Your solution makes sense - I might need some additional help in implementing it however. How exactly should I be linking the tables and what should I enter in the area below?

Cheers!