Table info:

ID = Key field
LastN = Last Name
FirstN = First Name
MiddN = Middle Name
SexHar = Sexual Harrassment Training (Date field)
StrMgmt = Stress Management Training (Date field)
AdvTrng = Advancement Training (Date field)

Here is the break-down: SexHar, StrMgmt, and AdvTrng are all training requirements. But SexHar and StrMgmt are required every calendar year, while AdvTrng is required every fiscal year. This is because it does not take “money” to do SexHar and StrMgmt training, but it costs actual funds to complete AdvTrng training. I only need to keep track of the last time each person has completed all training, so that is why I have it set up this way.

I need a report that does something like this:


I need my database to be able to:
Show green dates on the report for SexHar and StrMgmt that have occurred since Jan 1 THIS YEAR, and red dates for anything that occurred before then.
Show green dates for AdvTrng that have occurred since Oct 1, and red for anything that occurred before then. Here is the kicker. If today is March 21, 2011, then the date it should be referencing to is Oct 1, 2010 (October of “last year”…NOT “this year”). But if I run the report on October 21, 2011, it needs to go back to Oct 1, 2011 (NOT Oct 1, 2010) (that is…October of “this year” NOT “last year”).