Jul 09 2019 05:41 AM
Hello,
I been trying to figure how to create a query that deletes anyone that is not listed on a spreadsheet. I have a spreadsheet (UnitRosterReport 2) linked. I have one query that adds any person from the linked spreadsheet who is not listed on the table in access (UnitRosterReport). I am now trying to create another query that deletes any person with an Employee ID Number (Emplid) from the table that are no longer listed on the linked spreadsheet. The issue I am having is that I keep getting an error message stating “Specified the table containing the records you want to delete.” I have tried all different things I just can’t get it to work. Here is the code I am using:
DELETE UnitRosterReport.Emplid, UnitRosterReport.Rank, UnitRosterReport.Grade, UnitRosterReport.[Member Last Name], UnitRosterReport.[Member First Name], UnitRosterReport.[ATU OPFAC], UnitRosterReport.[Exp Loss Dt], UnitRosterReport.[Exp AD Term Dt], UnitRosterReport.[Rotate Dt], UnitRosterReport.Birthdate, UnitRosterReport.[AD Base Dt], UnitRosterReport.[Date of Rank], UnitRosterReport.[Report Date], [UnitRosterReport 2].[Sea Pay Prem Dt], UnitRosterReport.[BAH/BAQ], UnitRosterReport.[RES Screen Dt], UnitRosterReport.[**bleep** Sea Time YY MM DD], UnitRosterReport.[Mar Stat], UnitRosterReport.[Last Good Con Dt], UnitRosterReport.[Position Nbr], UnitRosterReport.Gender, UnitRosterReport.[Leave Balance], [UnitRosterReport 2].Emplid, UnitRosterReport.*
FROM [UnitRosterReport 2] LEFT JOIN UnitRosterReport ON [UnitRosterReport 2].Emplid = UnitRosterReport.Emplid
WHERE ((([UnitRosterReport 2].Emplid) Is Null And ([UnitRosterReport 2].Emplid) Is Null));
This was my latest attempt. I got this by taking the append query for adding the new person and converting it to delete query instead. I just don’t know what I am missing. I bet is something very simple and I have search and tried different things for weeks and I keep getting the same message.
Any support would greatly be appreciate!
Jul 22 2019 08:45 AM
Maybe a query like this could help:
DELETE
FROM UnitRosterReport
WHERE NOT EXISTS (SELECT * FROM UnitRosterReport 2 WHERE UnitRosterReport 2.Emplid = UnitRosterReport.Emplid)
Jul 24 2019 08:51 AM
or like
DELETE * FROM UnitRosterReport
WHERE
UnitRosterReport.Emplid NOT in (SELECT Emplid from [UnitRosterReport 2]);
Jul 29 2019 11:19 AM
@testmuts Worked! I been trying to figure this out for months now. So simple and yet couldn't wrap myself around it. Thank you so much!