Deleting Old Records

%3CLINGO-SUB%20id%3D%22lingo-sub-744739%22%20slang%3D%22en-US%22%3EDeleting%20Old%20Records%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744739%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20been%20trying%20to%20figure%20how%20to%20create%20a%20query%20that%20deletes%20anyone%20that%20is%20not%20listed%20on%20a%20spreadsheet.%20I%20have%20a%20spreadsheet%20(UnitRosterReport%202)%20linked.%20I%20have%20one%20query%20that%20adds%20any%20person%20from%20the%20linked%20spreadsheet%20who%20is%20not%20listed%20on%20the%20table%20in%20access%20(UnitRosterReport).%20I%20am%20now%20trying%20to%20create%20another%20query%20that%20deletes%20any%20person%20with%20an%20Employee%20ID%20Number%20(Emplid)%20from%20the%20table%20that%20are%20no%20longer%20listed%20on%20the%20linked%20spreadsheet.%20The%20issue%20I%20am%20having%20is%20that%20I%20keep%20getting%20an%20error%20message%20stating%20%E2%80%9CSpecified%20the%20table%20containing%20the%20records%20you%20want%20to%20delete.%E2%80%9D%20I%20have%20tried%20all%20different%20things%20I%20just%20can%E2%80%99t%20get%20it%20to%20work.%20Here%20is%20the%20code%20I%20am%20using%3A%3CBR%20%2F%3E%3CBR%20%2F%3EDELETE%20UnitRosterReport.Emplid%2C%20UnitRosterReport.Rank%2C%20UnitRosterReport.Grade%2C%20UnitRosterReport.%5BMember%20Last%20Name%5D%2C%20UnitRosterReport.%5BMember%20First%20Name%5D%2C%20UnitRosterReport.%5BATU%20OPFAC%5D%2C%20UnitRosterReport.%5BExp%20Loss%20Dt%5D%2C%20UnitRosterReport.%5BExp%20AD%20Term%20Dt%5D%2C%20UnitRosterReport.%5BRotate%20Dt%5D%2C%20UnitRosterReport.Birthdate%2C%20UnitRosterReport.%5BAD%20Base%20Dt%5D%2C%20UnitRosterReport.%5BDate%20of%20Rank%5D%2C%20UnitRosterReport.%5BReport%20Date%5D%2C%20%5BUnitRosterReport%202%5D.%5BSea%20Pay%20Prem%20Dt%5D%2C%20UnitRosterReport.%5BBAH%2FBAQ%5D%2C%20UnitRosterReport.%5BRES%20Screen%20Dt%5D%2C%20UnitRosterReport.%5B**bleep**%20Sea%20Time%20YY%20MM%20DD%5D%2C%20UnitRosterReport.%5BMar%20Stat%5D%2C%20UnitRosterReport.%5BLast%20Good%20Con%20Dt%5D%2C%20UnitRosterReport.%5BPosition%20Nbr%5D%2C%20UnitRosterReport.Gender%2C%20UnitRosterReport.%5BLeave%20Balance%5D%2C%20%5BUnitRosterReport%202%5D.Emplid%2C%20UnitRosterReport.*%3CBR%20%2F%3EFROM%20%5BUnitRosterReport%202%5D%20LEFT%20JOIN%20UnitRosterReport%20ON%20%5BUnitRosterReport%202%5D.Emplid%20%3D%20UnitRosterReport.Emplid%3CBR%20%2F%3EWHERE%20(((%5BUnitRosterReport%202%5D.Emplid)%20Is%20Null%20And%20(%5BUnitRosterReport%202%5D.Emplid)%20Is%20Null))%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20was%20my%20latest%20attempt.%20I%20got%20this%20by%20taking%20the%20append%20query%20for%20adding%20the%20new%20person%20and%20converting%20it%20to%20delete%20query%20instead.%20I%20just%20don%E2%80%99t%20know%20what%20I%20am%20missing.%20I%20bet%20is%20something%20very%20simple%20and%20I%20have%20search%20and%20tried%20different%20things%20for%20weeks%20and%20I%20keep%20getting%20the%20same%20message.%3CBR%20%2F%3EAny%20support%20would%20greatly%20be%20appreciate!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-744739%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-767908%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20Old%20Records%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-767908%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372909%22%20target%3D%22_blank%22%3E%40luitron%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20a%20query%20like%20this%20could%20help%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDELETE%3CBR%20%2F%3EFROM%20UnitRosterReport%3CBR%20%2F%3EWHERE%20NOT%20EXISTS%20(SELECT%20*%20FROM%20UnitRosterReport%202%20WHERE%20UnitRosterReport%202.Emplid%20%3D%20UnitRosterReport.Emplid)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-771865%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20Old%20Records%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771865%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372909%22%20target%3D%22_blank%22%3E%40luitron%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%20like%3C%2FP%3E%3CP%3EDELETE%20*%20FROM%20UnitRosterReport%3C%2FP%3E%3CP%3EWHERE%3C%2FP%3E%3CP%3EUnitRosterReport.Emplid%20NOT%20in%20(SELECT%20Emplid%20from%20%5BUnitRosterReport%202%5D)%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-777882%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20Old%20Records%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-777882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381533%22%20target%3D%22_blank%22%3E%40testmuts%3C%2FA%3E%26nbsp%3BWorked!%20I%20been%20trying%20to%20figure%20this%20out%20for%20months%20now.%20So%20simple%20and%20yet%20couldn't%20wrap%20myself%20around%20it.%20Thank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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!

3 Replies
Highlighted

@luitron 

 

Maybe a query like this could help:

 

DELETE
FROM UnitRosterReport
WHERE NOT EXISTS (SELECT * FROM UnitRosterReport 2 WHERE UnitRosterReport 2.Emplid = UnitRosterReport.Emplid)

 

Highlighted

@luitron 

 

or like

DELETE * FROM UnitRosterReport

WHERE

UnitRosterReport.Emplid NOT in (SELECT Emplid from [UnitRosterReport 2]);

 

 

Highlighted

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