I have been gifted access to a table that stores employee data. Each month a new record is added for the employee. There is a Date field which is actually a text field that I cannot change in the format of 20160601 (yyyy/mm/dd) The date is always the first of the month.
I am trying to find a way to return only the most recent record for each employee based off of this field. So if the employee is still with the company it would pull back the current month record and if an employee left 2 months ago it would return their record from 2 months ago as it is the most recent record for that employee.
I have tried man, min, first , last to no avail.
Has anyone done this before. Your help as always is appreciated.
Table Structure
Emp_no Short Text Last_name Short Text First_name Short Text Industry Short Text Manager Short Text DTT Short Text Terr_ind Short Text Team_key Short Text Ops_mgr_last_name Short Text Program_code Short Text Country_code Short Text Former_Territory Short Text Former_Program Short Text Date Short Text