[Walter Steadman]

Greetings all,
I have built a DB that tracks pilot hours and it is working (but very
clunky, and bad design). Here is where I am stuck.

Pilots hours are tracked over a 1 year time but in two six month blocks.
Block one starts the first day of the month following the birth month and
runs for 6 months. Second block starts when the first block ends and runs
through the last day of the birth month.
Example:
Name Birthdate 1st Semi Start 1st Semi End 2nd
Semi Start 2nd Semi End
John 15 Feb 66 1 March 31 August 1
September 28 February
Since March 1st has already passed (today being 29 June 05), it would be 1
March 05, 31 Aug 05, 1 Sep 05, 28 Feb 06

Dan 14 Oct 66 1 November 30 April 1
May 31 Oct
Since March has not passed yet it would be 1 November 04, 30 April 05, 1 May
05, 31 Oct 05

Notice how each pilots year is different based on Birth Month. At the start
of (1st Semi) pilots time goes back to 0

Example:
John and Dan both fly the following days.
15 Dec 04 4hrs
20 Dec 04 3hrs
13 Mar 05 1hrs
15 Mar 05 2hrs
10 Jun 05 5hrs
17 Jun 05 6hrs

When I do a total for each Semi it would look like this:

Name 1st Semi 2nd Semi
John 14 0
Dan 10 11

Noting that John's December hours do not count because they were from his
last years report.

How I am doing it now is by adding a field to my table called "startdate"
and then doing a few Append queries that make the start date either the (1st
Semi date using this year) if the 1st Semi Date has already passed or I make
the 1st Semi date using last year if their 1st Semi Date has not passed yet.
I know this is wrong, but I am stumped as to how to get these dates fluidly
while only inputting the Birthdate in the table. Should I get these dates
through a query? In a report? I am using the DateSerial Function and an
IIF statement to get the start date currently. I have added the code to my
Append Queries along with a list of the table names. This has been baffling
me for a while now and I would appreciate any advice on how to tackle this
issue.

TABLE: tblPilot
PilotID (PK)
LastName
FirstName
Birthdate mm,dd,yyyy

TABLE tblHours
HourID (AutoNumber, PK)
PilotID (FK to tblPilot)
DateFlew mm, dd, yyyy
HoursFlown Number field
Active Yes/No
1st Semi Yes/No

When the first form opens it contains the following code (OnOpen):

Private Sub Form_Open(Cancel As Integer)
CurrentDb.Execute "uqryStartDateCurrYear", dbFailOnError
CurrentDb.Execute "uqrySetStartDate", dbFailOnError
CurrentDb.Execute "uqryActive", dbFailOnError
CurrentDb.Execute "uqryActiveHours", dbFailOnError
CurrentDb.Execute "uqry1stsemiFalse", dbFailOnError
CurrentDb.Execute "uqry1stSemi", dbFailOnError
End Sub


I have listed the SQL for each above query below.

"uqryStartDateCurrYear"
UPDATE tblPilot SET tblPilot.StartDate =
DateSerial(Year(Date()),Month([birthmonth]),Day([birthmonth]));

"uqrySetStartDate"
UPDATE tblPilot SET tblPilot.StartDate =
DateSerial(Year(Date())-1,Month([birthmonth]),Day([birthmonth]))
WHERE (((Date())<DateSerial(Year(Date()),Month([birthmonth])+1,1)));

uqryActive
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
SET tblHours.Active = False;

uqryActiveHours
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
SET tblHours.Active = True
WHERE
(((tblHours.flyDate)DateSerial(Year([startdate]),Month([startdate])+1,0)));

uqry1stsemiFalse
UPDATE tblHours SET tblHours.[1stSemi] = False;

uqry1stSemi
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
SET tblHours.[1stSemi] = True
WHERE (((tblHours.flyDate) Between
DateSerial(Year([startdate]),Month([startdate])+1,0) And
DateSerial(Year([startdate]),Month([startdate])+7,1)));


Again, I know this is wrong and clunky as I am storing alot of unnecessary
data such as Active (Although it has other uses I think) and 1st Semi (I
only have that field to say If True/False you will be part of which Semi.

Any advice on how to accomplish this less WRONG would sincerely be
appreciated.

If this is posted to the wrong NewsGroup, I apologize.

TIA

--
Wally Steadman
US Army in Iraq