Comparing Promotion Requirements with courses

[ahmad_3011]

Hello,


The database attached a sample for a promotion system from the EMP form when you choose promotion needed to an employees the access should compare the Promotion Requirements with the courses taken by the employee, every promotion has a mandatory courses and optional courses, all mandatory has to be completed and at least the number of optional courses for the promotion and if courses taken not matched with courses the access highlights the missed courses or any other notification
thanks

[Dal Jeanis]

Is there an issue you are having, or are you just showing us your database as an example?

[ahmad_3011]

Hi Dal,
yes I'm showing a problem that I need access to determine if the employee passed the courses needed to his promotion or not in the form EMP

[ahmad_3011]

Can someone help with this

[Dal Jeanis]

ahmad - you've gotten pretty far along, and I'd be happy to help, but I need to understand the actual usage a little before I give you feedback.

Is this database for a real-world application, or is this an exercise for a class? I don't see any dates or history information, which I would expect in a real world HR application. I don't want to just anwer your question, and leave you with a database that won't work for your business.

[Dal Jeanis]

I've changed some of your names here for clarity. tables got tbl on the front, and Mandatory and Optional got Is or Min in front to show whether they were a yes/no or a count field.

tblCourses
  CourseID        (autokey)
  CourseName      ***Text***

tblEmps
  EmpID           (autokey)
  EmpName         ***Text***
  RankID          (foreign Key to tblRanks)
  PromotionID     (foreign Key to tblPromotions)

tblCourseLogs
  CourseLogID     (autokey)
  EmpID           (foreign Key to tblEmps)
  CourseID        (foreign Key to tblCourses)
  RankID          (foreign Key to tblRanks)

tblPromReqs
  PromReqID       (Autokey)
  PromotionID     (foreign Key to tblPromotions)          
  CourseID        (foreign Key to tblCourses)
  IsMandatory     (Yes/No)

tblPromotions
  PromotionID     (AutoKey)
  PromotionName   ***Text***
  MinMandatory    (Number of Mandatory Courses)
  MinOptional     (Number of Optional Courses)

tblRanks
  RankID          (Autokey)
  RankName        ***Text***

Some notes about Issues that I see in the database structure -

1) The ranks don't have any real order. The RankID is an autokey, so the key itself should not imply anything about which rank might be better than which other rank. If the company decides to add another rank, or decides to implement a different ladder, then the database will have to change.

For a real world application, I would suggest that each tblPromotions record should have two ranks associated with it - the old rank that you must be in order to get that promotion, and the new rank that you will be after that promotion. If people could get the same promotion starting from two different ranks, then the structure will need to be modified.

2) tblLogs, obviously, should have a date that the Course was completed.

3) Technically, the minimum number of Mandatory Courses for a promotion is redundant data on tblPromotions, since it can be determined by a simple query against tblPromReqs on PromotionID. Every time someone adds or deletes a mandatory tblPromReqs record, or changes and existing tblPromReqs record from Mandatory to optional or vice versa, that number will change.

4) On the tblEmps record, you have PromotionID. I don't understand what that might be. If this is a record of the last promotion, wouldn't that be implicit in her current Rank? If it's the next one she's trying for, does it really belong on the database, and does it belong on the tblEmps record, or somewhere else?

For recording the prior promotion, I would recommend implementing a tblPromLogs table to track the date of the last promotion of each employee. So, I'd suggest these changes to the above table structure -

tblCourseLogs
  CourseLogID     (autokey)
  EmpID           (foreign Key to tblEmps)
  CourseID        (foreign Key to tblCourses)
  RankID          (foreign Key to tblRanks)
  LogDate         ***Date*** 

tblPromLogs
  PromLogID       (autokey)
  EmpID           (foreign Key to tblEmps)
  PromotionID     (foreign Key to tblPromotions)
  PromDate        ***Date*** 

tblPromotions
  PromotionID     (AutoKey)
  PromotionName   ***Text***
  OldRankID       (foreign Key to tblRanks)
  NewRankID       (foreign Key to tblRanks)
  MinMandatory    (Number of Mandatory Courses)  {we'll consider deleting this one later}
  MinOptional     (Number of Optional Courses)

Some questions about your envisioned procedure for promotion -

If the same course is required for promotion from rank 1 to rank 2, and from rank 2 to rank 3, does the course have to be taken a second time?

When a staff member achieves a promotion, do the records that gained them that promotion go away, or are they retained fofr future potential promotions?

Can a staff member be considered for two different promotions at the same time, or does their current rank determine a single promotion that is the only one available?

[ahmad_3011]

Hi Dal,
Thank you for your response and your amazing analysis, actually this is a sample database I created to make it easy to show my point but I have a bigger and complicated one all your noted are right and I have some notes:
1- the RankID is only an information about the employee contract and we only have two ranks, the employee may have the same course in rank 1 and rank 2 for many reasons and it would not have any effect for the employee promotion the important that the employee passed the course regardless of his rank
2- The PromotionID in the emp table in my case will work that way for some reason please ignore it Shift+R improves the quality of this image. Shift+A improves the quality of all images on this page..
3- The employee may take the course in rank 1 or rank 2 the important at least to pass the course once and it would not make in difference if he toke the course in the two ranks
4- The most important note you have mentioned that actually the employees may have 1 or 2 or 3 promotions, so I changed the promotionID in EMP for to Multivalues, so please if it possible that you work that way even if I chose any number of promotions he let me know what courses are missing by anyway in the form.

thank you very very much

[Dal Jeanis]

1. okay, so rank has nothing to do with promotion. But you told me that the employee may have a *course* in different ranks. So, you are saying that the course is taught different ways for each rank? Can the same employee every have a course in Rank 1 and the same course in rank 2? Is there a business reason for differentiating the Rank of a Course an employee has taken?

2. and an employee can have multiple promotions.

I can tell you right off that multivalued fields is a capital-B Bad Idea. Put the Promotions in a separate table.


3. And, you want for the form to be able to determine ALL courses necessary for the employee to receive ALL the promotions that you have selected?

[ahmad_3011]

Hi Dal,
no matter the course taken in any rank and its possible to repeat the course again
Yes an employee can have more than one promotion
in the form I need - based on the selected promotion(s) - the form tells me what are the missing courses for the employee to be able to join the promotion(s) be coloring the missing courses in the promotion list or any other ways

[ahmad_3011]

Hi Dal,
Please give a look in this, one of my friends tried to solve this, but what is missing is to highlight the missing courses to make it easy to review the promotions, also please try to find a solution to make the promotion list multivalued

[ahmad_3011]

Hi Dal, I think you are right about the multivalue list I will leave it like that and create another field to store the second promotion

[Dal Jeanis]

Put promotions in a separate table, please. You'll save sooooo many headaches for yourself and others.

[ahmad_3011]

It would be very hard because, all i can do is to have one promotion for the employee

[Dal Jeanis]

No, it's easy. You're already doing the same thing for Courses.

Each record in tblPromLogs represents one promotion completed by an employee. An employee can have many promotions. Many employees can get the same promotion.

tblPromLogs
   PromLogID   Autokey
   EmpID         Foreign Key to tblEmps    
   PromotionID Foreign Key to tblPromotions
   PromDate    ***date***
   PromNotes   ***Text***

[ahmad_3011]

Hi Dal,
in the system I use is like Recruitment systems the employee has many courses the promotion is like a suggested company so when I chose promotion I match this company requirements with the courses he has, so the employee here when he matches the requirements for the promotion we start to the process of contract with this operation.