Semi-Annual Totals from Database

[Guest]

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.

 

[Steve Schapel]

Chris,

First of all, I wouldn't be using a DLookup function here. Presumably,
the tblFlightDatabase table contains a field that identifies the pilot,
on the basis of which the tblPersonalData table could be joined into the
query. And then I would make another simple 2-field table, 12 records,
to show the 3-character month abbreviation and the corresponding month
number, and also add this table to the query and join to the pilot's
BirthMonth field. And then the date criteria can be written like this
(caution: untested!)...
Between
DateSerial(Year(Date())+(Month(Date())<MonthNumber),MonthNumber+1,1) And
DateAdd("m",6,DateSerial(Year(Date())+(Month(Date())<MonthNumber),MonthNumber+1,1)-1
--
Steve Schapel, Microsoft Access MVP

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.

 

[Marshall Barton]

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.

Note that you need the conditions in the WHERE clause, not
the HAVING clause. The HAVING clause is for conditions on
the result of the Sum(...) calculations, the WHERE clause
restricts the data that is used in the Sum(...)
calculations.

As for your Between expression, you are hampering this by
having a month name in the personal data table instead of
the month number. It is almost always better to have the
table save the actual birthdate so you can then use an
appropriate function (Month) to extract the desired part and
use the DateSerial function to calculate the next month (or
whatever). When you use a month name, you can not use +1 to
get to the next month. Because of this, you need to
concatenate the various parts of the date into a string and
then convert the string to a date value.

Between DateAdd("m", 1, CDate("1 " & DLookup ("Birthmonth",
"tblPersonalData") & " " & Year(Date())))
And DateAdd("m", 6, CDate("31 " & DLookup("Birthmonth",
"tblPersonalData") " " & Year(Date()))

Note that your use of Year(Date()) is probably not what you
really want here. Maybe you want to use
Year(DateAdd("m", -6, Year(Date()))
but I don't know exactly what you want to do with this part
of your problem.

 

[Guest]

Marshall-

Thank you for your response. I tried the code that you gave me with no
luck. It kept giving me a "Data Type Mismatch Error". I messed around a bit
with the DatePart function and came up with a query that would extract the
birthmonth from the Personal Data table and compute the start and end dates
for the current semi annual period based on the current date. Here is the
SQL:

SELECT IIf(DatePart("m",Date()) & "/" & DatePart("d",Date()) & "/" &
DatePart("yyyy",Date())<DatePart("m",DateAdd("m",-6,DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]")))
& "/" & DatePart("d",1) & "/" &
DatePart("yyyy",Date()),DatePart("m",DateAdd("m",1,DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]")))
& "/" & DatePart("d","2") & "/" &
DatePart("yyyy",Date()),DatePart("m",DateAdd("m",-5,DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]")))
& "/" & DatePart("d","2") & "/" & DatePart("yyyy",Date())) AS [Semi Annual
Start], DatePart("m",DateAdd("m",5,[Semi Annual Start])) & "/" &
DatePart("d","1") & "/" & DatePart("yyyy",Date()) AS [Semi Annual End];

As you can see, it checks to see if the current date is prior to the start
of the second six month period, and if true, sets the active date range to
the beginning of the first six month period (The first day after the end of
the birthmonth). If the user's birthmonth is November (11) and the current
date is in January, the Start date should be December 1 of the previous year.
The code works fine except for the fact that it is not changing the year if
that six month period is in a prior or new year. I would appreciate any help
that you could provide me with my code.

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.

Note that you need the conditions in the WHERE clause, not
the HAVING clause. The HAVING clause is for conditions on
the result of the Sum(...) calculations, the WHERE clause
restricts the data that is used in the Sum(...)
calculations.

As for your Between expression, you are hampering this by
having a month name in the personal data table instead of
the month number. It is almost always better to have the
table save the actual birthdate so you can then use an
appropriate function (Month) to extract the desired part and
use the DateSerial function to calculate the next month (or
whatever). When you use a month name, you can not use +1 to
get to the next month. Because of this, you need to
concatenate the various parts of the date into a string and
then convert the string to a date value.

Between DateAdd("m", 1, CDate("1 " & DLookup ("Birthmonth",
"tblPersonalData") & " " & Year(Date())))
And DateAdd("m", 6, CDate("31 " & DLookup("Birthmonth",
"tblPersonalData") " " & Year(Date()))

Note that your use of Year(Date()) is probably not what you
really want here. Maybe you want to use
Year(DateAdd("m", -6, Year(Date()))
but I don't know exactly what you want to do with this part
of your problem.

 

[Marshall Barton]

Thank you for your response. I tried the code that you gave me with no
luck. It kept giving me a "Data Type Mismatch Error". I messed around a bit
with the DatePart function and came up with a query that would extract the
birthmonth from the Personal Data table and compute the start and end dates
for the current semi annual period based on the current date. Here is the
SQL:

SELECT IIf(DatePart("m",Date()) & "/" & DatePart("d",Date()) & "/" &
DatePart("yyyy",Date())<DatePart("m",DateAdd("m",-6,DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]")))
& "/" & DatePart("d",1) & "/" &
DatePart("yyyy",Date()),DatePart("m",DateAdd("m",1,DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]")))
& "/" & DatePart("d","2") & "/" &
DatePart("yyyy",Date()),DatePart("m",DateAdd("m",-5,DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]")))
& "/" & DatePart("d","2") & "/" & DatePart("yyyy",Date())) AS [Semi Annual
Start], DatePart("m",DateAdd("m",5,[Semi Annual Start])) & "/" &
DatePart("d","1") & "/" & DatePart("yyyy",Date()) AS [Semi Annual End];

As you can see, it checks to see if the current date is prior to the start
of the second six month period, and if true, sets the active date range to
the beginning of the first six month period (The first day after the end of
the birthmonth). If the user's birthmonth is November (11) and the current
date is in January, the Start date should be December 1 of the previous year.
The code works fine except for the fact that it is not changing the year if
that six month period is in a prior or new year. I would appreciate any help
that you could provide me with my code.

I don't think I can see the problem you're asing about
because there is so much clutter in there. For instance,
the expression:
DatePart("m",Date()) & "/" & DatePart("d",Date()) & "/" &
DatePart("yyyy",Date())
results in a string that looks like "1/2/2006", which will
not compare the way you want with another similar string
because you omitted the CDate function.

Furthermore, your use of DatePart("d","1") will not return
anything related to what you are doing (it always returns
31).

You also need to stop adding all that extra (and incorrect)
syntax to the DLookup function.

Before we work on whatever the year issue is, lets try to
get the basics straight. Assuming, you are refusing to
listen to Steve's idea of using another table to translate
the the birth month abbreviation to a number (would greatly
simplify this mess as well as speed it up dramatically), I
think you can start from something more like:

SELECT IIf(DatePart("m",Date()) < DatePart("m",
DateAdd("m",-6,DLookUp("Birthmonth","tblPersonalData")
& "/1/" & DatePart("yyyy", Date()))),
CDate(DateAdd("m",1,DLookUp("Birthmonth","tblPersonalData")
& "/1/" & DatePart("yyyy",Date()))),
CDate(DateAdd("m",-5,DLookUp("Birthmonth","tblPersonalData")
& "/1/" & DatePart("yyyy",Date())))) AS [Semi Annual Start],
CDate(DateAdd("m",5,[Semi Annual Start]) & "/31/" &
DatePart("yyyy",Date()))) AS [Semi Annual End]
FROM sometable

I'm pretty sure I have not unraveled all of it and I suggest
that you break it down futher to separate fields for each
component of the IIf so you can see what you are calculating
at each step of the calculation.

 

[Steve Schapel]

Chris

The code works fine

Are you sure? Without going into it in detail, I can't see that this
will work. For example, you are using this phrase in your SQL:
DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]")
.... which will only apply to the first record in the tblPersonalData
table, and my understanding is that this table will have more than one
record, and you will need to reference the BirthMonth for each pilot. No?
And another example, you have this phrase in your SQL...

DateAdd("m",-5,DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]"))
.... which I can't see how this will work. The DateAdd function applies
to a date, and you are trying to make it work on a string. You are
trying, for example, to subtract 5 months from "NOV", which the human
brain can interpret your meaning, but Access can't.
As a further example, your sql includes this...
DatePart("d",1)
.... which will return 31 which is the Day() portion of day #1 which is
31-Dec-1899, and I can't imagine that this is what you are really intending.
So, did you actually try this SQL out?

... except for the fact that it is not changing the year if
that six month period is in a prior or new year.

I am pretty sure that the suggestion I made in my earlier reply will
handle this.

 

[Guest]

Steve,

The database that I am designing is for a single pilot to keep track of his
flight time and currencies. The tblFlightDatabase table contains only the
data for each individual flight, i.e. Date, Duty, etc...

I went with your suggestion (not that I was dismissing it earlier) and
created a query that pulled together the pilot's birthmonth from one table
and the flight data from another, and tried your code that you initially
suggested to filter the dates. I kept getting a data type mismatch error
when I tried to run the query, so I went into the table that contained the
dates for the flights and adjusted the format to "yyyy/mm/dd", and still no
luck. I had originally had the date format set to medium "dd-mmm-yy" and I
thought that may have been causing the problem. I don't have much experience
with Access and this is the first major project that I have tried to tackle.
I greatly appereciate the help that you have been providing.

Chris

Chris

The code works fine

Are you sure? Without going into it in detail, I can't see that this
will work. For example, you are using this phrase in your SQL:
DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]")
.... which will only apply to the first record in the tblPersonalData
table, and my understanding is that this table will have more than one
record, and you will need to reference the BirthMonth for each pilot. No?
And another example, you have this phrase in your SQL...

DateAdd("m",-5,DLookUp("[tblPersonalData]![Birthmonth]","[tblPersonalData]"))
.... which I can't see how this will work. The DateAdd function applies
to a date, and you are trying to make it work on a string. You are
trying, for example, to subtract 5 months from "NOV", which the human
brain can interpret your meaning, but Access can't.
As a further example, your sql includes this...
DatePart("d",1)
.... which will return 31 which is the Day() portion of day #1 which is
31-Dec-1899, and I can't imagine that this is what you are really intending.
So, did you actually try this SQL out?

... except for the fact that it is not changing the year if
that six month period is in a prior or new year.

I am pretty sure that the suggestion I made in my earlier reply will
handle this.

 

[Steve Schapel]

Chris,

The format of the flight date is not relevant. The format only affects
the way the data is displayed, and has no impact on its value.

Are you able to change the Date Type of the Birthmonth field in the
tblPersonalData table from Text to Number, and then enter 11 in there
instead of "NOV"?

Knowing that this database only pertains to one pilot puts a slightly
different complexion on it. This would also imply, I suppose, that
there is no field in the tblFlightDatabase table to identify the pilot,
since there is only one anyway . Will this database always only ever
be for this specific pilot?

 

[Guest]

Steve,

I finally got it worked out. I used your code with a few extra things and
came up with this:

SELECT DLookUp("Birthmonth","tblPersonalData") AS Birthmonth,
DatePart("yyyy",DateAdd("m",-[Birthmonth],Date())) AS [Start of Current
Flying Year], DateSerial([Start of Current Flying Year],[Birthmonth]+1,1) AS
[Annual Start Date], IIf(Date()<DateAdd("m",6,[Annual Start Date]),[Annual
Start Date],DateAdd("m",6,[Annual Start Date])) AS [Semi Annual Start Date],
DateAdd("m",6,[Semi Annual Start Date])-1 AS [Semi Annual End Date],
DateAdd("m",12,[Annual Start Date])-1 AS [Annual End Date]
FROM tblPersonalData;

It seems to work the way that I had intended. I tested it with several
birthmonths and even changed the computer system time and it seems to return
the proper data every time. I just use the values from the Semi Annual Start
Date and Semi Annual End Date in my search filter. Works great. I really
appreciate the advice that you provided. Thanks again!

Chris