C
Chris
I am designing a Flight-Hour tracking program for pilots. One of the queries
that I am trying to design involves calculating the sum of flight hours
during a six month period based on the user's birthmonth. If the user's
birthmonth is November, I would like to be able to show the total flight
hours between December 1 and May 31 of the current semi-annual period so that
they can determine if they have met their minimum requirements. Here is the
SQL that I am using in the query:
SELECT DISTINCTROW tblFlightDatabase.[Aircraft Flown] AS Aircraft,
tblFlightDatabase.[Date of Flight], tblFlightDatabase.Simulator,
tblFlightDatabase.[RL 1], Sum(tblFlightDatabase.D) AS [Sum Of D],
Sum(tblFlightDatabase.DS) AS [Sum Of DS], Sum(tblFlightDatabase.N) AS [Sum Of
N], Sum(tblFlightDatabase.NG) AS [Sum Of NG], Sum(tblFlightDatabase.NS) AS
[Sum Of NS], Sum(tblFlightDatabase.H) AS [Sum Of H], Sum(tblFlightDatabase.W)
AS [Sum Of W], Nz([Sum Of D],0)+Nz([Sum Of DS],0)+Nz([Sum Of N],0)+Nz([Sum Of
NG],0)+Nz([Sum Of NS],0)+Nz([Sum Of H],0)+Nz([Sum Of W],0) AS Total
FROM tblFlightDatabase
GROUP BY tblFlightDatabase.[Aircraft Flown], tblFlightDatabase.[Date of
Flight], tblFlightDatabase.Simulator, tblFlightDatabase.[RL 1]
HAVING (((tblFlightDatabase.[Aircraft
Flown])=DLookUp("[tblPrimaryAircraftData]![Aircraft
Designation]","[tblPrimaryAircraftData]")) AND
((tblFlightDatabase.Simulator)=False) AND ((tblFlightDatabase.[RL 1])=True))
ORDER BY tblFlightDatabase.[Aircraft Flown];
I am trying to extract the three-letter birthmonth value (i.e. "Nov") from
another table and insert it into the expression. Here is the Expression that
I am using to filter the dates:
Between #1/(DLookup ("[tblPersonalData]![Birthmonth]" ,
"[tblPersonalData]"))+1/Year(Date())# And #31/(DLookup
("[tblPersonalData]![Birthmonth]" , "[tblPersonalData]"))/Year(Date())+1#
I would like for the query to compute the dates for the calculation
automatically without the user having to enter the inclusive dates. I would
greatly appreciate any help that anyone could provide.
that I am trying to design involves calculating the sum of flight hours
during a six month period based on the user's birthmonth. If the user's
birthmonth is November, I would like to be able to show the total flight
hours between December 1 and May 31 of the current semi-annual period so that
they can determine if they have met their minimum requirements. Here is the
SQL that I am using in the query:
SELECT DISTINCTROW tblFlightDatabase.[Aircraft Flown] AS Aircraft,
tblFlightDatabase.[Date of Flight], tblFlightDatabase.Simulator,
tblFlightDatabase.[RL 1], Sum(tblFlightDatabase.D) AS [Sum Of D],
Sum(tblFlightDatabase.DS) AS [Sum Of DS], Sum(tblFlightDatabase.N) AS [Sum Of
N], Sum(tblFlightDatabase.NG) AS [Sum Of NG], Sum(tblFlightDatabase.NS) AS
[Sum Of NS], Sum(tblFlightDatabase.H) AS [Sum Of H], Sum(tblFlightDatabase.W)
AS [Sum Of W], Nz([Sum Of D],0)+Nz([Sum Of DS],0)+Nz([Sum Of N],0)+Nz([Sum Of
NG],0)+Nz([Sum Of NS],0)+Nz([Sum Of H],0)+Nz([Sum Of W],0) AS Total
FROM tblFlightDatabase
GROUP BY tblFlightDatabase.[Aircraft Flown], tblFlightDatabase.[Date of
Flight], tblFlightDatabase.Simulator, tblFlightDatabase.[RL 1]
HAVING (((tblFlightDatabase.[Aircraft
Flown])=DLookUp("[tblPrimaryAircraftData]![Aircraft
Designation]","[tblPrimaryAircraftData]")) AND
((tblFlightDatabase.Simulator)=False) AND ((tblFlightDatabase.[RL 1])=True))
ORDER BY tblFlightDatabase.[Aircraft Flown];
I am trying to extract the three-letter birthmonth value (i.e. "Nov") from
another table and insert it into the expression. Here is the Expression that
I am using to filter the dates:
Between #1/(DLookup ("[tblPersonalData]![Birthmonth]" ,
"[tblPersonalData]"))+1/Year(Date())# And #31/(DLookup
("[tblPersonalData]![Birthmonth]" , "[tblPersonalData]"))/Year(Date())+1#
I would like for the query to compute the dates for the calculation
automatically without the user having to enter the inclusive dates. I would
greatly appreciate any help that anyone could provide.