Unique Query

[KiowaPilot]

I track flight time for Aviators, who have minimums to maintain based on
their birthmonth. I am looking to automatically calculate on a report the
ammount of time that they have flown within a 6 month timeframe based on
their b-day, and the current date. For example Aug is my birthmonth and it is
Currently Feb. I would only want to calculate the data between 31 Aug and 1
Mar because todays date of 25 Feb falls between that range. And if todays
date were 3 Mar it would calculate the data between 28 Feb and 1 Sep.

I have two Tables; 1.Pilot Names

name Birthmonth
Joe Aug
Mike Jun

and 2. Flight Data

Date of Flight Name Duty Position Flight Status Time of Flight
3 Feb Joe Instructor Day 2.0
12 Mar Mike Copilot Night 1.5

 

[Allen Browne]

To make things easy, lets assume you can change the BirthMonth field to a
Number type, and store a value between 1 (Jan) and 12 (Dec.)

Now you want to calculate the reporting dates based on birthdate. If you
copy the function below and paste into a standard module, you could put an
expression like this in the Criteria row of your query, under the FlightDate
field:

= CalcStartDate(Nz([BirthMonth],1)) AND <

DateAdd("m", 6, CalcStartDate(Nz([BirthMonth],1)))
(That's all on one line.)

I have assumed here that your example was both-dates-exclusive, i.e. you
wanted 1 Sep - 28 Feb (both dates inclusive); otherwise there would be
overlaps between the two. If that's not what you want, you can adjust the
criterion to:

= CalcStartDate(Nz([BirthMonth],1)) - 1 AND <

DateAdd("m", 6, CalcStartDate(Nz([BirthMonth],1))) + 1

The function adds 7 months to the birthmonth of the current year. If that's
future, it subtracts 6 months until we get a past date, which is the last
completed 6-month period for that birthdate. We then subtract 6 months to
get the start of that period.


Function CalcStartDate(iBirthMonth As Integer) As Date
Dim dtEnded As Date
dtEnded = DateSerial(Year(Date), iBirthMonth + 7, 1)
Do While dtEnded > Date
dtEnded = DateAdd("m", -6, dtEnded)
Loop
CalcDate = DateAdd("m", -6, dtEnded)
End Function


In your example, I assume you mean to exclude both dates

 

[karl dewey]

I changed field names due some of your are reserved words in Access. And
like Allen I used a numerical month.
Try the query below. It seems to test ok.
SELECT Pilot_Names.Pilot, Sum(Flight_Data.Flight_Time) AS SumOfFlight_Time
FROM Pilot_Names LEFT JOIN Flight_Data ON Pilot_Names.Pilot =
Flight_Data.Pilot
WHERE (((Flight_Data.Date_of_Flight) Between
DateSerial(Year(Date()),[BirthMon]+1,1) And
DateAdd("m",6,DateSerial(Year(Date()),[BirthMon]+1,0))))
GROUP BY Pilot_Names.Pilot
ORDER BY Pilot_Names.Pilot;
------------------------
I had thought it would need this query but the above seems to work when I
did limited testing.
SELECT Pilot_Names.Pilot, Sum(Flight_Data.Flight_Time) AS SumOfFlight_Time
FROM Pilot_Names LEFT JOIN Flight_Data ON Pilot_Names.Pilot =
Flight_Data.Pilot
WHERE (((IIf(Month(Date())<[BirthMon],1,0))=0) AND
((Flight_Data.Date_of_Flight) Between DateSerial(Year(Date()),[BirthMon]+1,1)
And DateAdd("m",6,DateSerial(Year(Date()),[BirthMon]+1,0)))) OR
(((IIf(Month(Date())<[BirthMon],1,0))=1) AND ((Flight_Data.Date_of_Flight)
Between DateAdd("m",[BirthMon]+6,DateSerial(Year(Date()),[BirthMon],1)) And
DateAdd("m",[BirthMon],DateSerial(Year(Date())+1,[BirthMon],0))))
GROUP BY Pilot_Names.Pilot
ORDER BY Pilot_Names.Pilot;

 

[KiowaPilot]

First, Thank you Karl and Allen, you have taken your time without reward to
help others and that is commendable.
But it is not working like I had envisioned. very close though.

I am trying more to calculate the flight time within a 6 month timeframe.
That timeframe is based on the Pilots Birthdate. That part is done, but the
code is not taking into account todays date that determines which of the two
6 month periods.
I believe that the year(Date()) in Dateserial is what is messing it up.
but If I really knew I would'nt be asking for help as I am now.

 

[Michael Gramelspacher]

I am trying more to calculate the flight time within a 6 month timeframe.
That timeframe is based on the Pilots Birthdate. That part is done, but the
code is not taking into account todays date that determines which of the two
6 month periods.
I believe that the year(Date()) in Dateserial is what is messing it up.
but If I really knew I would'nt be asking for help as I am now.

Maybe you should generate a table holding all the six-month periods for each pilot.

Say pilot's birth date is 12 Aug 1981

The 1st period is 31 Aug 1981 to 1 Mar 1982

I assume qualifying flight hours would be > 31 Aug 1981 and < 1 Mar 1982. Is that correct?

Obviously, you only care about the six-month periods during which the pilot is assigned to your
unit.

 

[karl dewey]

A couple of things do not seem correct --
-- You said six months but your example dates - 31 Aug and 1 Mar - is six
months and two days.
-- Your six months periods always start after birth month or after the six
months anniversary of their mirth month. Starting that month no one would
have met their minimums. Should it not be past six months rather than the
future?

 

[KiowaPilot]

In a different query on an individual basis that was too much to manage for
30 Pilots I used a criteria of >#02/28/09# And <#09/01/09#. It would not
include March 1st and Aug 31 any other way.
This is what I did come up with so far using a crosstab query: I did change
some of the table and column names, and had to create some colums for Start
and End dates of the six month periods in the Pilots Table.


TRANSFORM Sum(Flight_Input.HRS) AS SumOfHRS
SELECT Flight_Input.LName, Sum(Flight_Input.HRS) AS [Total Of HRS]
FROM Pilots LEFT JOIN Flight_Input ON Pilots.Lname=Flight_Input.Lname
WHERE (((Flight_Input.FLT_Date) Between [Start1stSemi] And [End1stSemi]) AND
((Date())<>False)) OR (((Flight_Input.FLT_Date) Between [Start2ndSemi] And
[End2ndSemi]) AND ((Date())<>False))
GROUP BY Flight_Input.LName
PIVOT Flight_Input.FS_ID;

 

[Michael Gramelspacher]

In a different query on an individual basis that was too much to manage for
30 Pilots I used a criteria of >#02/28/09# And <#09/01/09#. It would not
include March 1st and Aug 31 any other way.
This is what I did come up with so far using a crosstab query: I did change
some of the table and column names, and had to create some colums for Start
and End dates of the six month periods in the Pilots Table.


TRANSFORM Sum(Flight_Input.HRS) AS SumOfHRS
SELECT Flight_Input.LName, Sum(Flight_Input.HRS) AS [Total Of HRS]
FROM Pilots LEFT JOIN Flight_Input ON Pilots.Lname=Flight_Input.Lname
WHERE (((Flight_Input.FLT_Date) Between [Start1stSemi] And [End1stSemi]) AND
((Date())<>False)) OR (((Flight_Input.FLT_Date) Between [Start2ndSemi] And
[End2ndSemi]) AND ((Date())<>False))
GROUP BY Flight_Input.LName
PIVOT Flight_Input.FS_ID;

Wouldn't it be better to have a table of Qualification Periods rather than
Start1stSemi and Start2Semi?

CREATE TABLE [Qualification Periods] (
pilot_id LONG NOT NULL
REFERENCES Pilots (pilot_id),
period_start DATETIME NOT NULL,
period_end DATETIME NOT NULL,
PRIMARY KEY (pilot_id, period_start));

Current period criteria is simply
period_start <= Date() AND period_end >= Date()