Webmaster Forum / ASP / Database Access / December 2007

Hi, can you help me with some SQL logic, a chemistry company must realize
some employees exams, there are 4 exams types:

1) when employee is hired
2) when employee is fired
3) when employee had the first contact with some component
4) and periodic exams, every 3 months, 6 months, etc

and I need to build a sql that fills the employe_exam_schedule based on exam
type:

the user system chooses the year, example 2007, clicks a button, the
schedule table must be filled with all 2007 exams records that the employees
should realize.

initially all status will be 0, and another step a user system will check
the status for each employee on a asp web-system if employee made or not the
exam... if there is status 1, the record must be skiped because the exam was
done...

employe_exam_schedule table:
ID_SEQ ->      autonumber
ID_Employe -> number
CodExam    -> number
DateShouldRealizeExam -> DateTime
Status -> bit ( 0 -> exam not realized / 1 -> exam realized)

EXAM table:

CodExam    -> number
ExamName -> char
IS_PERIODIC -> bit (yes/no)
MonthPeriodicity -> SmallInt -> example every 3 months, etc...
IS_First_CONTACT -> bit(yes/no)
IS_Hired -> bit(yes/no)
IS_Fired -> bit(yes/no)

Employee TABLE:
ID_Employe number
DateHire DateTime
DateFire DateTime
----------------------------------------

What you suggest?

Thanks!