Results 1 to 8 of 8
  1. #1
    pulse225 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    4

    Assigning a value to a Date field based on seniority


    First time poster and limited Access user, so bear with me.
    I have a Hire Date field in a table of employess. Multiple records/employees have the same Hire Date. I want to create an additional field that will assign a value of 1 to the earliest date, 2 to the next, and so on. The end result should be that all employees with the same Hire Date will have the same seniority value. Ideally, this value should adjust as records/employees are added or deleted. Any simple solutions?

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    What is the end goal here? What do you intend to do with that value?
    If we know what you are hoping to use it for, we may be able to offer other solutions.

    But perhaps ranking will give you what you need.
    See http://allenbrowne.com/ranking.html

  3. #3
    pulse225 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    4
    The goal is to see a Seniority Value in a list of the employees - the most senior emplyee(s) with a value of 1. Obviously we can sort the emplyee list by Hire Date and see the seniority, but when sorted alphabetically or by any other field, we would like to see a simple value that represents their seniority.
    Example:

    Last Name, First Name, Hire Date, Seniority
    Duck, Daffy, 3/24/06, 1
    Duck, Donald, 5/16/08, 2
    Mouse, Mickey, 3/24/06, 1
    Mouse, Minnie, 2/05/13, 3

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I actually found an article that looks like it shows precisely what you are trying to do, rank by hire date.
    See example 1 in this article here: https://support.microsoft.com/en-us/kb/208946

  5. #5
    pulse225 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    4
    Thanks Joe. The expression in Example 1 seems to be what I'm looking for. Only problem is my Northwind sample does not include HireDate in the Employee table, so still trying to figure out the proper syntax for my query.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Just substitute your table/field names in there. I followed the steps through and was able to do it.

    Here is the SQL code of the query I came up with:
    Code:
    SELECT Emp1.[Last Name], Emp1.[First Name], Emp1.[Hire Date], 
    (Select Count(*) from MyTable Where [Hire Date] < [Emp1].[Hire Date]+1;) AS Seniority
    FROM MyTable AS Emp1
    ORDER BY Emp1.[Hire Date];
    You would just need to replace all instances of "MyTable" with the name of your table.

  7. #7
    pulse225 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    4
    Thanks again Joe. I got it to work. Still have to figure out a little fine tuning, but it's exactly what I was hoping for.
    Seems my error in the expression was the underscore in the Where condition. Just made my boss's day!

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Excellent! Glad you got it working!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 10-24-2017, 11:07 AM
  2. Replies: 4
    Last Post: 04-25-2015, 04:17 PM
  3. Seniority List Query with Two Date Fields
    By Nuke1096 in forum Access
    Replies: 5
    Last Post: 06-03-2014, 10:52 AM
  4. Replies: 2
    Last Post: 05-22-2014, 06:11 PM
  5. using ADO, assigning a date variable
    By crowegreg in forum Programming
    Replies: 3
    Last Post: 08-23-2011, 12:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums