Do I need code for this?

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
What is your table structure, looks like you are using access more like excel
do you add an extra column for each semester??, however if i am wrong you
could run a query that ignores Nulls and then count the number of records
returned for each person.
 
If Phil guessed right then your structure needs to look like this --
Instructuor ---
InstrID Autonumber - primary key
SSN: 123456789
FN: Carlos
LN: Torres
OtherInfo:

InstructorWork ---
InstrID number - integer - foreign key
Title: Instructor
Dept. English
YEAR: 2000
SEMESTER: Spring
PAY_RATE: $40.00

Set a one-to-many relationship between the tables.
 
Back
Top