Dates! Dates! Dates! Im stressing over Date codes. HELP?

[airbus2]

Hi guys!!
I have a real brain teaser with dates on my database. I am trying to place the right codes in the respective fields. OK, here we go.

I am keeping track of 6 date fields:
1)WBT_Initial (Web Based Training Date Initially completed)
2)WBT_Recurrent (Date taken aproximatly 1 year after initial)
3)Practice_Initial (Hands-on test initally completed)
4)Practice_Recurrent (Hands-on taken again 1 year later)
5)Complete (Date Practice_Initial was completed, or date Practice_Recurrent was completed)
6)Compliant_Date(365 days after Complete date. Employee must take the recurrent WBT as well as the recurrent Practice before this date so they will stay compliant)

Since we are just starting this, the recurrent fields wont be entered for a year from now.
Rule:
1)WBT_Initial field must be entered before Pracitice_Initial date can be entered.However they can be done on the same day.
2)Recurrent dates can not be before Initial dates.
3)The Compliant date is one year(365 days) after the Complete Date.

Example, an employee took the Initial Web based training module (WBT)yesterday (4-30-2008), and takes the Initial Practice (hands-on) test today (5-01-2008). His "Complete" date would = todays date (5-01-2008). His WBT-Recurrent date would be 4-30-2009, and his Practice-Recurrent date would be 5-01-2009. The Compliant date would be (5-01-2009)

I hope your still with me after all that.
Please help me figure this thing out.

 

[lespaul]

so do you have a table that looks like this:

[tt]
EmpID WBT_Initial WBT_Recurrent Practice_Initial etc
1 4/30/2008 5/1/2008 [/tt]

or do you have a table like this:

[tt]
EmpID TestCode TestDate
1 WBT_Initial 4/30/2008
1 Practice_Initial 5/1/2008[/tt]




Leslie

Have you met Hardy Heron?

 

[airbus2]

It's the first example. All the above fields are listed under "tblCourses". There are over 50 different courses listed in this table. All have the same premise: Initial Date, Recurrent Date, Compliant Date. "Compliant" is a bound filed not found in the table, with the code like

=IIf([HazmatRecurrent] Is Null,[HazmatInitial]+730,[HazmatRecurrent]+365)

This particular set of fields for the WBT/Practice are unique in that, you cant do the practice until you have done the WBT, and the course as a whole (i.e "Complete") is not complete until both WBT and Practice are complete. I made "Compliant" a field within "tblCourses", not sure if it should be like the other Compliants and be a bound field on the form.

 

[lespaul]

if possible, I would suggest changing the table structure to the second one, that's a more normalized solution. have you read Fundamentals of Relational Database Design?

Leslie

Have you met Hardy Heron?