Results 1 to 6 of 6
  1. #1
    AmberH is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2017
    Posts
    1

    Database with hierarchy fields that could change

    Hello!

    I am attempting to setup an access database, and everything that I want to do is pretty straightforward except for the hierarchy piece. We have sales agents who are part of a team that is broken down as such:

    State Manager (Top)


    Regional Manager
    District Manager
    Agent (Bottom)

    However, the team can change throughout the course of the year, and we need to be able to track the team at a given point in time. For example, if Agent Johnny's District Manager was Bobby the week-ending January 13th, but then Agent Johnny was moved to District Manager Fred's team the week of April 1st, I would want to be able to show/track that Johnny was part of Bobby's team for part of the year, and then moved to Fred's team.

    My unique identifier for agents is their agent ID, which is assigned to an agent when they join the company. Has anyone ran across a situation like this that has parts that can be dynamic, and do you have any suggestions on how to work with this?

    Thanks!
    Amber

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Depends in the level of accuracy you require

    At the simplest level you will need to include a 'from' date in a many to many link table e.g.

    ID..DepartmentID...AgentID..FromDate
    1...1.....................1............01/01/2017
    2...1.....................1............

    The problem can be if the FromDate is not updated promptly, but say 2 months later. Not a problem on the face of it, but if you needed a view of the data as it was 1 month after the move (but also 1 month before the data is updated) then you also need a timestamp field so you can create a view based on the timestamp.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    I agree with Ajax though with a slightly different design. The core table I would suggest be:

    Autonumber, Name, Position, Start Date, End Date

    Name would be a look up of a table that is just a List table of employee names
    Position would be a look up of a table that is just a List table of positions

    With this structure you can then query who is in what position on any given date. One thing to be careful with is that when a Person changes position that their End Date of the position 1 is not the same date as the Start Date of position 2. Otherwise they'll be in 2 positions at the same time and that will be problematic in reporting.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't disagree about having the end date, but I would argue it is not necessary to be completed except perhaps for the final record when the agent leaves the company. Otherwise the period relating to a single department is the difference between the start date and the start date of the next department. The problem with completing the end date for each department is that it is a form of calculated value - with a risk of not only an overlap, but also leaving a gap.

    The way I handle it is just use the start date field, and when the agent leaves the company, the departmentID is zero (or perhaps a negative number to indicate why leaving such as resigned, fired, redundancy - even temporarily such as long term sick, sabbatical etc

    Anyway Amberti, a couple of suggestions for you, you decide which is right for you

  5. #5
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Well - it is a choice. The end date is either "implied" or it is "literal". I believe the literal is only minimum upfront work and makes all the downstream query/reporting somewhat easier and the human presentation is typically better satisfied with a literal end date. I always use it - this topic comes up in contracts, reservations, leases, etc.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    always more than one way to skin a cat

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

Similar Threads

  1. Electrical Hierarchy
    By HelpDesk in forum Access
    Replies: 55
    Last Post: 06-26-2015, 06:51 AM
  2. Creating Hierarchy
    By Just_Some_Guy in forum Access
    Replies: 17
    Last Post: 07-24-2013, 07:06 AM
  3. Using a hierarchy table
    By younggunnaz69 in forum Queries
    Replies: 3
    Last Post: 07-18-2012, 10:11 PM
  4. Hierarchy summing
    By dskysmine in forum Queries
    Replies: 17
    Last Post: 06-19-2012, 04:08 AM
  5. product hierarchy
    By Lata in forum Access
    Replies: 13
    Last Post: 08-03-2011, 02:56 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