Is an Instructor eligible for a salary increment?

[mauricioze via AccessMonster.com]

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.

 

[John W. Vinson]

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.

Well... the first problem here is that you are "Committing Spreadsheet", a
venial sin punishible by being required to read Codd and Date on database
normalization.

Do you change the structure of your table every semester, adding new fields?
OUCH!

A much better design would recognize that there is a many to many relationship
between Instructors and Semesters, with THREE tables:

Instructors
SSN: 123456789
FN: Carlos
LN: Torres
Title: Instructor
Dept. English

Semesters
Semester Spring I 2000
StartDate 1/9/2000
EndDate ...

TeachingAssignments
SSN: 123456789
Semester: Spring I 2000
PayRate: $40

with one RECORD - not field! - per semester taught. With this design your
query becomes very simple - a Totals query on TeachingAssignments, grouping by
SSN and counting records.

With your current non-normal design, you'll need a complex query adding across
fields. Since True is represented as -1, False as 0 you could use:

SELECT SSN, 18 + IsNull([Spring I 2000]) + IsNull([Spring II 2000]) +
IsNull([Fall I 2000]) + <and so on through all 18 fields>

This will start with 18 points and subtract 1 for each semester that is NULL.

I'd REALLY recommend using a "Normalizing Union Query" to migrate your data
into a properly normalized structure, though! Something like

SELECT SSN, "Spring I 2000", [Spring I 2000] AS PayRate
FROM yourtable
WHERE [Spring I 2000] IS NOT NULL
UNION ALL
SELECT SSN, "Spring II 2000", [Spring II 2000]
FROM yourtable
WHERE [Spring II 2000] IS NOT NULL
UNION ALL
SELECT SSN, "Fall 2000", [Fall 2000]
FROM yourtable
WHERE [Fall 2000] IS NOT NULL
UNION ALL
<etc etc to pick up all 18 fields>

Save this query as uniAllSemesters; view the data and see if it's picking
things up right. Then create TeachingAssignments and run

INSERT INTO TeachingAssignments(SSN, Semester, PayRate)
SELECT SSN, Semester, PayRate FROM uniAllSemesters;


John W. Vinson [MVP]

 

[John Spencer]

IF you can't or won't fix your table design, you can use the VBA function
below.
Copy and paste it into a VBA module and save the module with the name
"modRowMath"

You really should fix your structure as John Vinson mentioned. Otherwise,
you would need to modify the query each time you added another session or
needed to look over a different range of sessions.

Then in a query you can use the function to count non-null values

Field: TimesTaught: fRowCount([Spring I 2000],[Spring II 2000], ..., [Fall I
2003])
Criteria: >=6

'==============CODE STARTS ======================
Public Function fRowCount(ParamArray Values()) As Variant
'================================================================
' Procedure : fRowCount
' Created : 12/6/2006
' Author : John Spencer
' Purpose : Count whether or not items passed have a value.
'
' Max of 29 arguments can be passed to a function in Access SQL
' workaround is to add fRowCount to fRowCount for large
groups of fields.
'================================================================

Dim i As Integer, iCount As Integer

For i = LBound(Values) To UBound(Values)
If IsNull(Values(i)) = False Then
iCount = iCount + 1
End If
Next i

fRowCount = iCount
End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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.

Well... the first problem here is that you are "Committing Spreadsheet", a
venial sin punishible by being required to read Codd and Date on database
normalization.

Do you change the structure of your table every semester, adding new
fields?
OUCH!

A much better design would recognize that there is a many to many
relationship
between Instructors and Semesters, with THREE tables:

Instructors
SSN: 123456789
FN: Carlos
LN: Torres
Title: Instructor
Dept. English

Semesters
Semester Spring I 2000
StartDate 1/9/2000
EndDate ...

TeachingAssignments
SSN: 123456789
Semester: Spring I 2000
PayRate: $40

with one RECORD - not field! - per semester taught. With this design your
query becomes very simple - a Totals query on TeachingAssignments,
grouping by
SSN and counting records.

With your current non-normal design, you'll need a complex query adding
across
fields. Since True is represented as -1, False as 0 you could use:

SELECT SSN, 18 + IsNull([Spring I 2000]) + IsNull([Spring II 2000]) +
IsNull([Fall I 2000]) + <and so on through all 18 fields>

This will start with 18 points and subtract 1 for each semester that is
NULL.

I'd REALLY recommend using a "Normalizing Union Query" to migrate your
data
into a properly normalized structure, though! Something like

SELECT SSN, "Spring I 2000", [Spring I 2000] AS PayRate
FROM yourtable
WHERE [Spring I 2000] IS NOT NULL
UNION ALL
SELECT SSN, "Spring II 2000", [Spring II 2000]
FROM yourtable
WHERE [Spring II 2000] IS NOT NULL
UNION ALL
SELECT SSN, "Fall 2000", [Fall 2000]
FROM yourtable
WHERE [Fall 2000] IS NOT NULL
UNION ALL
<etc etc to pick up all 18 fields>

Save this query as uniAllSemesters; view the data and see if it's picking
things up right. Then create TeachingAssignments and run

INSERT INTO TeachingAssignments(SSN, Semester, PayRate)
SELECT SSN, Semester, PayRate FROM uniAllSemesters;


John W. Vinson [MVP]

 

[mauricioze via AccessMonster.com]

Dear John Vinson and John Spencer.

Let me start by thanking you both for taking your time to reply to my post.
Your contribution always help us see the light at the end of the tunnel.

J. Vinson - Unfortunately, you are correct. It is OUCH every semester.
Just for the record this is a database that was just brought to my attention
the "designer" could not do the trick.
To be fair, the designer had no other choice. The data comes from a mainframe
system and it is in that format. I guess it would have taken more time to
create
the many to many relationship that you are suggesting. Sometimes this type of
projects
are given at the 11th hour. Unfortunately, I am afraid that because of time
constrains
I can't redesign the table structure. I will definitely consider your
suggestion if time will not be a killer at this time.

J. Spencer: I am so glad there was a plan B. your VBA function seems to be
doing the
trick. I followed your steps. I have never worked with VBA fucntions (the
object module is always
very intimidated one) I also created the query against my main table. it
works!
I have been double checking the results...so far so good.
It looks like this….

TimesTaught: fRowCount([SP_1_2000],[SP_2_2000],[FA_1_2000],[SP_1_2001],
[SP_2_2001],[FA_1_2001],[SP_1_2002],[SP_2_2002],[FA_1_2002])

with criteria >=6

As you can see J. Spencer, I am only selecting 3 years at a time to
accommodate my business rule.

Question: I also noticed that for some reason whoever was entering data was
actually putting ZEROS instead of valid rate of pays.
how do i tell the query no to count ZEROS but to keep doing whatever is doing
correct?

J. Spencer: I am sorry but as I said I am not very good with VBA function and
there is something I do not understand in the
purpose of your function. You wrote: Max of 29 arguments can be passed to a
function in Access SQL
workaround is to add fRowCount to fRowCount for large groups of fields.
Please elaborate on this... should I be concern with something on my data?

J. Vinson and J Spencer... I am so grateful you guys visit these group
discussions. Your contributions are very much appreciated by all of us.

Thank you,
MRZ

IF you can't or won't fix your table design, you can use the VBA function
below.
Copy and paste it into a VBA module and save the module with the name
"modRowMath"

You really should fix your structure as John Vinson mentioned. Otherwise,
you would need to modify the query each time you added another session or
needed to look over a different range of sessions.

Then in a query you can use the function to count non-null values

Field: TimesTaught: fRowCount([Spring I 2000],[Spring II 2000], ..., [Fall I
2003])
Criteria: >=6

'==============CODE STARTS ======================
Public Function fRowCount(ParamArray Values()) As Variant
'================================================================
' Procedure : fRowCount
' Created : 12/6/2006
' Author : John Spencer
' Purpose : Count whether or not items passed have a value.
'
' Max of 29 arguments can be passed to a function in Access SQL
' workaround is to add fRowCount to fRowCount for large
groups of fields.
'================================================================

Dim i As Integer, iCount As Integer

For i = LBound(Values) To UBound(Values)
If IsNull(Values(i)) = False Then
iCount = iCount + 1
End If
Next i

fRowCount = iCount
End Function
[quoted text clipped - 106 lines]

John W. Vinson [MVP]

 

[John Spencer]

To not count zeroes, you would have to force the zeroes to null or
modify the function.

TimesTaught: fRowCount(IIF([SP_1_2000]>0,1,Null),
IIF([SP_2_2000]>0,1,Null), IIF([FA_1_2000]>0,1,Null),
IIF([SP_1_2001]>0,1,Null), IIF([SP_2_2001]>0,1,Null),
IIF([FA_1_2001]>0,1,Null), IIF([SP_1_2002]>0,1,Null),
IIF([SP_2_2002]>0,1,Null), IIF([FA_1_2002]>0,1,Null))

Unless you have more than 29 arguments to the function at one time there
is not problem.

If you had to check 40 fields of data, then you could do something like

fRowCount(<<20 fields>>) + fRowCount(<<20 more fields>>)

There is still the problem that you could run into the character limit
for a "Cell" in the query grid.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Dear John Vinson and John Spencer.

Let me start by thanking you both for taking your time to reply to my post.
Your contribution always help us see the light at the end of the tunnel.

J. Vinson - Unfortunately, you are correct. It is OUCH every semester.
Just for the record this is a database that was just brought to my attention
the "designer" could not do the trick.
To be fair, the designer had no other choice. The data comes from a mainframe
system and it is in that format. I guess it would have taken more time to
create
the many to many relationship that you are suggesting. Sometimes this type of
projects
are given at the 11th hour. Unfortunately, I am afraid that because of time
constrains
I can't redesign the table structure. I will definitely consider your
suggestion if time will not be a killer at this time.

J. Spencer: I am so glad there was a plan B. your VBA function seems to be
doing the
trick. I followed your steps. I have never worked with VBA fucntions (the
object module is always
very intimidated one) I also created the query against my main table. it
works!
I have been double checking the results...so far so good.
It looks like this….

TimesTaught: fRowCount([SP_1_2000],[SP_2_2000],[FA_1_2000],[SP_1_2001],
[SP_2_2001],[FA_1_2001],[SP_1_2002],[SP_2_2002],[FA_1_2002])

with criteria >=6

As you can see J. Spencer, I am only selecting 3 years at a time to
accommodate my business rule.

Question: I also noticed that for some reason whoever was entering data was
actually putting ZEROS instead of valid rate of pays.
how do i tell the query no to count ZEROS but to keep doing whatever is doing
correct?

J. Spencer: I am sorry but as I said I am not very good with VBA function and
there is something I do not understand in the
purpose of your function. You wrote: Max of 29 arguments can be passed to a
function in Access SQL
workaround is to add fRowCount to fRowCount for large groups of fields.
Please elaborate on this... should I be concern with something on my data?

J. Vinson and J Spencer... I am so grateful you guys visit these group
discussions. Your contributions are very much appreciated by all of us.

Thank you,
MRZ

IF you can't or won't fix your table design, you can use the VBA function
below.
Copy and paste it into a VBA module and save the module with the name
"modRowMath"

You really should fix your structure as John Vinson mentioned. Otherwise,
you would need to modify the query each time you added another session or
needed to look over a different range of sessions.

Then in a query you can use the function to count non-null values

Field: TimesTaught: fRowCount([Spring I 2000],[Spring II 2000], ..., [Fall I
2003])
Criteria: >=6

'==============CODE STARTS ======================
Public Function fRowCount(ParamArray Values()) As Variant
'================================================================
' Procedure : fRowCount
' Created : 12/6/2006
' Author : John Spencer
' Purpose : Count whether or not items passed have a value.
'
' Max of 29 arguments can be passed to a function in Access SQL
' workaround is to add fRowCount to fRowCount for large
groups of fields.
'================================================================

Dim i As Integer, iCount As Integer

For i = LBound(Values) To UBound(Values)
If IsNull(Values(i)) = False Then
iCount = iCount + 1
End If
Next i

fRowCount = iCount
End Function

Dear Collegue:

[quoted text clipped - 106 lines]

John W. Vinson [MVP]