Safety Training Database - view creation

[bud31047]

I am working on a SQL Server database to track OSHA Safety and company mandated training compliance.
The training is conducted 13 times a year (each period). There are 100+ topics that we track. Each period we may train on multiple topics. We may also train select individuals on topics that are not part of a set schedule. We have a training schedule that we use, which may or may not change each year.

Here are the highlights of my table structure and data;

EmployeeTbl
EmpID EmpName Department
-------------------------
1 Bob Shipping
2 Mary Maintenance

TopicTbl
TopicID TopicName
-----------------
1 Forklift Safety
2 Hearing Conservation
3 Ergonomics
4 Fall Protection

ScheduleTbl
SchID SchYear SchPeriod TopicID
-------------------------------------
1 2011 1 2
2 2011 2 1
3 2011 2 3
4 2012 1 4

TrainingTbl
RecordID EmpID TrainingDate TopicID
-----------------------------------
1 1 01/01/2011 2
2 2 01/01/2011 2
3 1 01/25/2011 1
4 1 01/25/2011 3
5 2 01/25/2011 3
6 1 01/01/2012 4
7 2 01/01/2012 4

I need to extract Period and Year from TrainingDate so I will also have;

View_Training
RecordID EmpID TrainingDate TopicID TrnPeriod TrnYear
------------------------------------------------------------
1 1 01/01/2011 2 1 2011
2 2 01/01/2011 2 1 2011
3 1 01/25/2011 1 2 2011
4 1 01/25/2011 3 2 2011
5 2 01/25/2011 3 2 2011
6 1 01/01/2012 4 1 2012
7 2 01/01/2012 4 1 2012


My challenge;
Create a view(‘s) that shows if each employee has or has not been trained on each safety topic in the schedule. We conduct make-up training so an employee is considered trained if they make-up period 1 training, for example, during a later period.
Any suggestions or help creating a view I can build a compliance report from would be greatly appreciated.