Results 1 to 8 of 8
  1. #1
    ewuchatka is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    4

    Update and keep track of employees details over time

    Hello

    I am completely new to Microsoft Access. I've tried to use Excel for what I am trying to achieve, but I think it needs a more robust database approach.

    I would like to create a simple database.

    Fields:
    First Name
    Last Name
    Unique ID
    Job Role
    Job Level
    Location
    Month

    I would like to keep track of employees and any changes happening to them such as: job role, job level and location (and to be able to pull out data by month)

    I would like to set up a database, so that external users (line manager) can go to the form: Select their employee, change a job level and select a commencement date of the change (e.g. June).

    This will mean that from June onwards, this employee will show with a new job level, but if we pull out the data for May the same year, the old job title will show.

    I would also like people to be able to add a new record (new starter).

    How do I start? Please help



    Thank you

    Ewa

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You have your tEmployee table.
    you may want a tHistory table. This would hold the history of that employees jobs....
    EmpID
    JobRoll
    JobLevel
    StartDate

    this is a child table to the Employee table, 1 to Many.
    the tEmp table would hold the current position.
    the employee name would only occur once.

  3. #3
    ewuchatka is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    4

    Thanks

    Quote Originally Posted by ranman256 View Post
    You have your tEmployee table.
    you may want a tHistory table. This would hold the history of that employees jobs....
    EmpID
    JobRoll
    JobLevel
    StartDate

    this is a child table to the Employee table, 1 to Many.
    the tEmp table would hold the current position.
    the employee name would only occur once.

    Ranman256, I am very grateful for your help. Access is very new to me, but I think I can understand the principle.
    Do you think I will need many History tables then (because any detail of the person might change e.g. location, role, level) or a single table hold historical information? (you said 1 to many which makes me think that for any category e.g location and role, there will be a separate table) Apologies if this information is basic, but I've never even created a simple table in Access.

    Do you think as a novice I will be able to create what I am trying to achieve?

    Would you be able to recommend a link with videos/training on access?

    Thanks

    Ewa

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    watch youtube videos on 1-many relationships.
    you need 2 tables: the master (tPerson) and the child (tHistory)
    1 person can have many history records.

    you can create a tHistory table and it provides a history of that persons job and the time frame.
    this allows allows payment to see what job he did on what date. The rate could change depending on job title for that date.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How do I start? Please help
    Work through one or two of these tutorials. You have to do the work, but you will learn about database design. And what you learn can be used with any database. Each tutorial will take about 45 -60 minutes to complete.
    http://www.rogersaccesslibrary.com/T...lationship.zip
    http://www.rogersaccesslibrary.com/T...nformation.zip
    http://www.rogersaccesslibrary.com/T...getsDesign.zip

    Good luck. Let us know how you do.

  6. #6
    ewuchatka is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    4

    Thank you

    Quote Originally Posted by orange View Post
    Work through one or two of these tutorials. You have to do the work, but you will learn about database design. And what you learn can be used with any database. Each tutorial will take about 45 -60 minutes to complete.
    http://www.rogersaccesslibrary.com/T...lationship.zip
    http://www.rogersaccesslibrary.com/T...nformation.zip
    http://www.rogersaccesslibrary.com/T...getsDesign.zip

    Good luck. Let us know how you do.
    Orange and Ranman

    Thanks both for your help and support. I will go through these links and hope to build it.

    Can I also ask you how it works in terms of:
    1. Updates - can you create a form in access that allows users to update the database? The key requirements is easy and intuitive to use. I assume that the form is built within access? Sorry, I don't know much how access work so I start from zero.
    2. Reporting - is it possible in access to pull data so that you can identify changes in the data in the specified period (e.g. a list of period and changes that happened in a specified period)?

    I'm trying to understand how it works, so that before I invest more time into it, I explore all options.

    Thanks

    Ewa

  7. #7
    ewuchatka is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    4

    Progress on ERD

    Quote Originally Posted by orange View Post
    Work through one or two of these tutorials. You have to do the work, but you will learn about database design. And what you learn can be used with any database. Each tutorial will take about 45 -60 minutes to complete.
    http://www.rogersaccesslibrary.com/T...lationship.zip
    http://www.rogersaccesslibrary.com/T...nformation.zip
    http://www.rogersaccesslibrary.com/T...getsDesign.zip

    Good luck. Let us know how you do.
    Hello

    Apologies for the delay, it's been a busy time at work. Orange, the resources you sent were really useful and I used them to try to create my diagram. Thanks. This is the first time I've ever created one, would you mind having a look at the below?

    My narrative is the following:
    Company requires an employees tracker. They want to track information about employees and their job history. Employees information include their employees unique id, first name and last name. The job history includes a job title, FTE value, job level, line manager, site, region, team and for each of these fields a date is assigned to identify when the change happened.
    (Please note, none of the fields is not related apart from site and region (if the site is London, it will be always Europe) and line manager and team (Line manager and his employees are always part of the same team); what I mean is: job title is not correlated to a job level, the site is not correlated to line manager)

    I've gone through your files and based on this I created this ERD.

    Click image for larger version. 

Name:	Entity Relationship Diagram Draft.jpg 
Views:	7 
Size:	88.3 KB 
ID:	28349

    Best wishes
    Ewuchatka

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ewa,

    Quite impressive if this is your first ERD.
    A few questions/observations:

    FTE --full time equivalent --I'm not sure how this fits with Employee -does this mean an Employee could be part time, or is spread over different classifications? ==needs clarification

    TEAM -- seems to be missing. Can a line Manager have different Teams? How do you know who is on what Team at any point in time?

    Re Access -- I would not use spaces in any field or object names. Avoid special (nonalphanumeric characters) the "/" in table name (it will eventually cause you syntax issues).Also the Employee/Site might be better named EmployeeWorksAtSite, similarly with Employee/Job Level --> EmployeeHasJobLevel (no spaces, no slash, and the real meaning of the junction table). You can use the underscore("_") in Access without issue.

    Do you need EffectiveDate for every entity and relationship??? Employee X became an Employee on Date1, he/she was on TeamA on Date2;he/she became Line Manager on Date3; Employee X was move to DepartmentD on Date4; then was assigned to Manage TeamC on Date 5, and was transferred to RegionZ with a new Title on Date 6...etc. etc

    It also seems possible (but may not be relevant) that Job Levels could change at the whim of management, so JobLevelEffectiveDate may be a potential need.

    Line Manager is also Employee, right?

    You may find my stump the model useful.


    I'm not trying to be negative -you've done a good job - my concern is "What facts have to be obtained from and therefore recorded (or calculated from data) in your database".


    Good luck with your project.

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

Similar Threads

  1. Replies: 4
    Last Post: 11-08-2016, 07:49 PM
  2. Replies: 1
    Last Post: 05-21-2016, 08:51 AM
  3. Replies: 7
    Last Post: 11-04-2015, 04:10 PM
  4. Question: Table Design - How to track time from multiple entry?
    By warhead92100 in forum Database Design
    Replies: 5
    Last Post: 08-12-2015, 02:04 AM
  5. Replies: 3
    Last Post: 04-17-2012, 03:26 PM

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