Remove Records based on Date Condition

Hi Anonymous 

Not sure if this the best solution, but it's at most a doable workaround.

• Create a duplicate of the table and rename it something like Before 2022
• In the orginal table filter the Job_Start_date >= 01/01/2022
• Untick enable query for the duplicate table.
• Create a custom column called lets say Cut Off and Enter "01/01/2022" and convert to date
• Highlight the PersonalID and in the Ribbon, Choose Group By
• Click on Advanced
• Rename the Column to MaxDate
• Click on Add aggregation
• Call the new column All and the Operation should be All Rows
• Press ok

   

• 
• In the Max column you will see the Max Job-Start_Date
• Expand the All column except for the PersonalID
• Create a filter on the Max date column <= doesn't matter what date you choose, we will adapt the code next
• Change the code after the each part to 

each [Max] < [Cut Off])​

• This will filter all Employees who have a max Job_Start_Date before 01/01/2022
• Remove the Max Date and Cut Off Columns

Once you have this return to the original table and in the ribbon, click on append queries and choose the duplicated table to append.

This will give you the result you need.

Thanks

Joe

If this post helps, then please Accept it as the solution