DELETE QRY FROM MASTER TABLE?

[UberZoot]

Ladies and Gents,
I have a system that tracks individual's records in several areas, and each of these areas has a source table. When this person leaves the organization, I have append queries that archive his/her records into corresponding separate tables. How can I set up a Delete query to delete an individuals records from th main tables after they are appended to the archives? The criteria for each person is their social security number. I tried setting up a select query with the SSN in a "Master Del Table" and joining this to the other tables. I received all types of error messages. Basically, when a person leaves, I want to copy their data and then remove it from the operating system. I'm still just a tech user at this point, so I appreciate your guidance. Any and all help will be greatly appreciated.

UZ

 

[psemianonymous]

Delete queries don't allow you to use JOINs with other tables, for some reason. I know, I don't agree either, and yes, it's a pain. There are workarounds:

DELETE * FROM [your_main_table_name] WHERE
[EMPLOYEE_ID_FIELD] IN 
 (SELECT [EMPLOYEE_ID_FIELD] FROM [qryComplexQuery])

So the keywords you're looking for in this context is the "fieldname IN (SELECT statement)" clause. Basically what you do is create "qryComplexQuery" that figures out everyone who needs to be deleted. It just lists all the employees needing deletion.

Then in the actual delete query, you use the above syntax to poach the list of 'deleteables' and check and see if the current record is in that list.