Pull up only recent dates from employees who have multiple records with multiple date

[sandra1]

i have an employee database,my employees have multiple change forms with diff dates, but i only want to find the most recent date from each employee. How do i do that?

[Seth Schrock]

Welcome to Bytes.

Depends on how you are wanting to view the information. In a query? In a textbox? We need more information to be able to properly help you.

[sandra1]

i want to view it in a query

[sandra1]

i have a query with fields of, first name, last name, work cell number, work cell type, is active and change form date. So when i run it all the dates from the change form date field come up, bt i only want to see the most recent date of each employee.

[Seth Schrock]

Can you post the query's SQL code (See Before Posting (VBA or SQL) Code). If your data is properly normalized, then it will be very simple to do. If not (then it should be) we will have to do a little trick to get it to work. Your SQL code will tell me which method is needed.

[sandra1]

SELECT Employee.FirstName, Employee.LastName, EmployeeInfo.WorkCellNumber, EmployeeInfo.WorkCellType, Employee.IsActive, EmployeeInfo.ChangeFormDate
FROM Employee INNER JOIN EmployeeInfo ON Employee.EmployeeID = EmployeeInfo.EmployeeID;

[Seth Schrock]

Wonderful. Okay, this is just air code as I don't have a system up to be able to test it, but this should be close if not perfect.

SELECT E.FirstName
, E.LastName
, EI.WorkCellNumber
, EI.WorkCellType
, E.IsActive
, EI.ChangeFormDate
FROM Employee As E INNER JOIN 
    (
        SELECT EmployeeID
        , WorkCellNumber
        , WorkCellType
        , Max(ChangeFormDate)
        FROM EmployeeInfo
        GROUP BY EmployeeID
    ) As EI
    ON E.EmployeeID = EI.EmployeeID

[sandra1]

There must be something im doing wrong, because it wont let me run it, it keeps sayin syntax error in JOIN operation.

[Seth Schrock]

Please post what you have using the [CODE/] button to add the required code tags for you.

You might try running just the subquery on it own. Just copy the code between the parenthesis into a new query window and then try to run it and see if there is an error.