Date Issue for viewiing

[Walter Steadman]

Greetings all,
I am trying to work on a DB to allow Aviators to track the number of
hours they fly in a given flight period. I have the following tables built:

tblAviator (contains the following fields)
AviatorID (PRI KEY)
LastName
FirstName
BirthMonth
DutyStatus

tblHoursFlown (contains the following fields)
hoursID (PRI KEY) Autonumber
AviatorID (used for relationship)
Date
HoursFlown
TypeFlown

My relationship is a 1 to Many with 1 Aviator having flown many different
hours. I can get this to work fine. I need to build a report that will
show FLIGHT PERIODs and this is where it gets tricky. A flight period is
defined as a 6 month period that begins on the first day of the month
following your birth month and ending on the last day of the 6th month after
your birth month. Example: Birthmonth is JAN START Period is 1 Feb 05, END
period is 31 July 05 and the start date of the Second 6 month period would
be 1 August 05 to 31 Jan 06.

So would it be wise to add additional columns to my table where I input the
Flight Periods for each Aviator?

So the report would look something like the following:

Aviator Name BirthMonth DutyStatus #hours flown this
period (This period being whatever period they are in as based on above
months criteria)

Example Hours follows:

Smith 27 May 05 3
Stone 27 May 05 2
Jones 27 May 05 4
Smith 15 Jan 05 4
Stone 15 Jan 05 2
Jones 15 Jan 05 3
Smith 28 May 05 2
Smith 29 May 05 4

Smiths Birthday is in Jan so his Flight Periods are from 1 Feb to 31 Jul and
1 Aug to 31 Jan so in the report, for current period it would count all of
his hours from 1 Feb through 31 July which would total 9. It would not
count the hours he flew in Jan 05 because they are not in this current
period.

I know this is long and I apologize, I just don't know how or where to work
that math out. Table, Report, Query etc... any help would be appreciated.
As it is now, they are tracking many pilots on a spreadsheet but that is
limited by having only 255 columns.

TIA
Wally Steadman
Operation Iraqi Freedom
Tikrit, Iraq

 

[Marshall Barton]

I am trying to work on a DB to allow Aviators to track the number of
hours they fly in a given flight period. I have the following tables built:

tblAviator (contains the following fields)
AviatorID (PRI KEY)
LastName
FirstName
BirthMonth
DutyStatus

tblHoursFlown (contains the following fields)
hoursID (PRI KEY) Autonumber
AviatorID (used for relationship)
Date
HoursFlown
TypeFlown

My relationship is a 1 to Many with 1 Aviator having flown many different
hours. I can get this to work fine. I need to build a report that will
show FLIGHT PERIODs and this is where it gets tricky. A flight period is
defined as a 6 month period that begins on the first day of the month
following your birth month and ending on the last day of the 6th month after
your birth month. Example: Birthmonth is JAN START Period is 1 Feb 05, END
period is 31 July 05 and the start date of the Second 6 month period would
be 1 August 05 to 31 Jan 06.

So would it be wise to add additional columns to my table where I input the
Flight Periods for each Aviator?

So the report would look something like the following:

Aviator Name BirthMonth DutyStatus #hours flown this
period (This period being whatever period they are in as based on above
months criteria)

Example Hours follows:

Smith 27 May 05 3
Stone 27 May 05 2
Jones 27 May 05 4
Smith 15 Jan 05 4
Stone 15 Jan 05 2
Jones 15 Jan 05 3
Smith 28 May 05 2
Smith 29 May 05 4

Smiths Birthday is in Jan so his Flight Periods are from 1 Feb to 31 Jul and
1 Aug to 31 Jan so in the report, for current period it would count all of
his hours from 1 Feb through 31 July which would total 9. It would not
count the hours he flew in Jan 05 because they are not in this current
period.

I know this is long and I apologize, I just don't know how or where to work
that math out. Table, Report, Query etc... any help would be appreciated.
As it is now, they are tracking many pilots on a spreadsheet but that is
limited by having only 255 columns.

I think you can determine the period by using the
DateSerial function, but I don't see where the appropriate
year would come from. For the current year, it would be:

FPStart: DateSerial(Year(Date()), BirthMonth + 1, 1)
FPEnd: DateSerial(Year(Date()), BirthMonth + 7, 0)

The next step is to put that into the report's record source
query to filter out all the dates outside the period of
interest:

SELECT A.LastName & ", " & A.FirstName As Aviator,
A.BirthMonth,
A.DutyStatus,
Sum(H.HoursFlown) As HoursFlown
FROM tblAviator As A LEFT JOIN tblHoursFlown As H
ON A.AviatorID = H.AviatorID
WHERE H.[Date] Between
DateSerial(Year(Date()), A.BirthMonth + 1, 1)
And DateSerial(Year(Date()), A.BirthMonth + 7, 0)
GROUP BY A.LastName & ", " & A.FirstName,
A.BirthMonth,
A.DutyStatus

At this point, the report should be trivial.