G
Guest
Dear Collegue:
I need to be able to do the following:
What I have: I have a table with 1658 records. Basically it is a list of
every instructor of the college that has taught in a period of 7 years. Each
year has 3 semesters.
For each semester that the instructor worked, the table records a $ amount
(Rate of pay)
Now, not all instructors taught every semester over those 7 years, so
basically I have a blank records.
See example:
SSN: 123456789
FN: Carlos
LN: Torres
Title: Instructor
Dept. English
Spring I 2000: $40.00
Spring II 2000: $40.00
Fall I 2000: $40.00
Spring I 2001: $45.00
Spring II 2001: $45.00
Fall I 2001: NULL
Spring I 2002: $50.00
Spring II 2002: $50.00
Fall I 2002: NULL
Spring I 2003: NULL
Spring II 2003 : NULL
Fall I 2003: NULL
Û·
Spring I 2006: NULL
Spring II 2006: NULL
Fall I 2006: NULL
NULL = Did not teach.
Objective: We need to calculate if the instructor is eligible for an
increment in his or her rate of pay base of the following statement:
***** Increments are due after teaching 6 semesters over a 3 year period.
******
Please note: The 3 years period doesn't need to be consecutive.
As you can see instructor Torres has been working since Spring I 2000, ON and
OFF, until Fall I 2006. He did worked more that six semesters, not
consecutive but indeed more than 6. This automatically qualifies him for an
increment in his rate of pay.
I need to somehow FLAG this business rule. I do not know where to start. Do I
need a query or just a report? If you recommend a query, how to you write
this business rules into it?
I would really appreciate your expertise in this scenario.
Thank you in advance for your time and effort.
MRZ.
I need to be able to do the following:
What I have: I have a table with 1658 records. Basically it is a list of
every instructor of the college that has taught in a period of 7 years. Each
year has 3 semesters.
For each semester that the instructor worked, the table records a $ amount
(Rate of pay)
Now, not all instructors taught every semester over those 7 years, so
basically I have a blank records.
See example:
SSN: 123456789
FN: Carlos
LN: Torres
Title: Instructor
Dept. English
Spring I 2000: $40.00
Spring II 2000: $40.00
Fall I 2000: $40.00
Spring I 2001: $45.00
Spring II 2001: $45.00
Fall I 2001: NULL
Spring I 2002: $50.00
Spring II 2002: $50.00
Fall I 2002: NULL
Spring I 2003: NULL
Spring II 2003 : NULL
Fall I 2003: NULL
Û·
Spring I 2006: NULL
Spring II 2006: NULL
Fall I 2006: NULL
NULL = Did not teach.
Objective: We need to calculate if the instructor is eligible for an
increment in his or her rate of pay base of the following statement:
***** Increments are due after teaching 6 semesters over a 3 year period.
******
Please note: The 3 years period doesn't need to be consecutive.
As you can see instructor Torres has been working since Spring I 2000, ON and
OFF, until Fall I 2006. He did worked more that six semesters, not
consecutive but indeed more than 6. This automatically qualifies him for an
increment in his rate of pay.
I need to somehow FLAG this business rule. I do not know where to start. Do I
need a query or just a report? If you recommend a query, how to you write
this business rules into it?
I would really appreciate your expertise in this scenario.
Thank you in advance for your time and effort.
MRZ.