MsAccess Delete all values but one in column by condition

I have a table in MS ACCESS 2013 that looks like this:

    Id        Department        Status           FollowingDept         ActualArea
  1000         Thinkerers       Thinking          Thinkerer             Thinkerer      
  1000         Drawers          OnDrawBoard       Drawers               Drawers
  1000         MaterialPlan     To Plan           MaterialPlan        MaterialPlan  
  1000         Painters         MatNeeded         MaterialPlan        
  1000         Builders         DrawsNeeded       Drawers

The table gives follow to an ID which has to pass through five departments, each department with atleast 5 different status.

Each status has a FollowingDept value, like *Department* Thinkerers has the status MoreCoffeeNow which means *FollowingDept* Drawers.

All columns except for ActualArea are columns which values are gotten from the feed of a query.

ActualArea is an Expr where I inserted this logic:

Iif(FollowingDept = Department, FollowingDept, "")

My logic is simple, if the FollowingDept and Department coincide, then the ID's ActualArea gets the value from FollowingDept.

But as you can see, there can be rare cases where an ID is like my example above, where 3 departments coincide with the FollowingDept. This cases are rare, but I would like to add something like a priority to Access.

Thinkerers has the top priority, then MaterialPlan, then Drawers, then Builders and lastly Painters. So, following the same example, after ActualArea gets 3 values, Access will execute another query or subquery or whatever, where it will evaluate each value priority and only leave behind that one with the top priority. So in this example, Thinkerers gets the top priority, and the other two values are eliminated from the ActualArea column.

Please keep in mind there are over 500 different IDs, each id is repeated 5 times, so the total records to evaluate will be 2500.

Solution

You need another table, with the possible values for actualArea and the priorities as numbers, and then you can select with a JOIN and order on the priority:

SELECT TOP 1 d.*, p.priority 
FROM departments d
LEFT JOIN priorities p ON d.actualArea = p.dept
WHERE d.id = 1000
AND p.priority IS NOT NULL
ORDER BY p.priority ASC

The IS NOT NULL clause eliminates all of the rows where actualArea is empty. The TOP condition leaves only the row with the top priority.

You don't seem to have a primary key for your table. If you don't, then I'll give another query in a minute, but I would strongly advise you to go back and add a primary key to the table. It will save you an incredible amount of headache later. I did add such a key to my test table, and it's called pID. This query makes use of that pID to remove the records you need:

DELETE FROM departments WHERE pID NOT IN (

   SELECT TOP 1 d.pID
   FROM departments d
   LEFT JOIN priorities p ON d.actualArea = p.dept
   WHERE id = 1000
   AND p.priority IS NOT NULL
   ORDER BY p.priority ASC

)

If you can't add a primary key to the data and actualArea is assumed to be unique, then you can just use the actualArea values to perform the delete:

DELETE FROM departments WHERE actualArea NOT IN (

   SELECT TOP 1 d.actualArea
   FROM departments d
   LEFT JOIN priorities p ON d.actualArea = p.dept
   WHERE id = 1000
   AND p.priority IS NOT NULL
   ORDER BY p.priority ASC

) AND id = 1000

If actualArea is not going to be unique, then we'll need to revisit this answer. This answer also assumes that you already have the id number.