Forum Discussion

luitron's avatar
luitron
Copper Contributor
Jul 09, 2019

Deleting Old Records

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!

  • testmuts's avatar
    testmuts
    Brass Contributor

    luitron 

     

    or like

    DELETE * FROM UnitRosterReport

    WHERE

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

     

     

    • luitron's avatar
      luitron
      Copper Contributor

      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!

  • Woldman's avatar
    Woldman
    Iron Contributor

    luitron 

     

    Maybe a query like this could help:

     

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

     

Resources