By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,519 Members | 1,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,519 IT Pros & Developers. It's quick & easy.

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

P: 5
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?
May 8 '14 #1
Share this Question
Share on Google+
8 Replies

Seth Schrock
Expert 2.5K+
P: 2,951
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.
May 8 '14 #2

P: 5
i want to view it in a query
May 8 '14 #3

P: 5
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.
May 8 '14 #4

Seth Schrock
Expert 2.5K+
P: 2,951
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.
May 8 '14 #5

P: 5
Expand|Select|Wrap|Line Numbers
  1. SELECT Employee.FirstName, Employee.LastName, EmployeeInfo.WorkCellNumber, EmployeeInfo.WorkCellType, Employee.IsActive, EmployeeInfo.ChangeFormDate
  2. FROM Employee INNER JOIN EmployeeInfo ON Employee.EmployeeID = EmployeeInfo.EmployeeID;
May 8 '14 #6

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT E.FirstName
  2. , E.LastName
  3. , EI.WorkCellNumber
  4. , EI.WorkCellType
  5. , E.IsActive
  6. , EI.ChangeFormDate
  7. FROM Employee As E INNER JOIN 
  8.     (
  9.         SELECT EmployeeID
  10.         , WorkCellNumber
  11.         , WorkCellType
  12.         , Max(ChangeFormDate)
  13.         FROM EmployeeInfo
  14.         GROUP BY EmployeeID
  15.     ) As EI
  16.     ON E.EmployeeID = EI.EmployeeID
May 9 '14 #7

P: 5
There must be something im doing wrong, because it wont let me run it, it keeps sayin syntax error in JOIN operation.
May 9 '14 #8

Seth Schrock
Expert 2.5K+
P: 2,951
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.
May 9 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.