Date Questions in a Query I think

[Walter Steadman]

Hello all,
I have posted a question similar to this before, but I did not
understand what the responder was conveying so going to post again.

I have a Database that has 3 tables.

tblPilots

PilotID (PK) this field is the First Letter of Last Name and last 4 of SSN -
S1234
LastName
FirstName
BirthMonthNumber
BirthDate
Status
FACNumber

tblHours
HourID (PK) Autonumber
PilotID (FK to tblPilots)
FlightDate
FlightHours

tblArchiveHours (This table is used to archive old dates)
HourID (PK) Autonumber
PilotID
FlightDate
FlightHours

In the tblPilots table, I have put a birth month number and a birthdate,
because I am not yet sure how to calculate what I am trying to calculate so
let me explain below....

A pilots hours are tracked in 2 6 month increments throughout the year. The
first increment starts on the 1st of the Month following the Birth Month
(example, birthdate is 19 Jan, then first increment starts 1 Feb and runs
through 31 July. Second increment starts with 1 August and runs through 31
Jan. This can be different for each pilot.)
I am trying to find a method for finding out these dates in a query I guess.

When I run a report (not yet created as of this post), I want to tell the
total number of hours a pilot has flown in this time period (so for example,
today is June 3, when I run the query on pilot above, it will tell me how
many hours the pilot has currently flown in his 1 Feb through 31 July time
period, because today falls in that time period). I hope I am being clear.
I have an Excel Spreadsheet with what I am looking for drawn out if anyone
wants to look at it. I also have the basic tables built in teh DB already
that I would be more than happy to show folks if it would help.

Any and all help would be appreciated.

Thanks in Advance

Wally Steadman
US Army in Iraq

 

[Rick B]

First, I would rethink my Pilot ID. It is a very bad idea to use an initial
in an ID. What if the person gets married? Divorced? Joins a heavy-metal
rock band and changes their name to "Satan"? Depending on the size of your
database, the last four of the social will probably always be unique. There
is a 1 in 9,999 chance that you will run into duplicates.


Secondly, there is no need to include a field for birth month. There are
several built-in functions to take the "Birthdate" field and pull out the
month. For example, you can use Month(BirthDate) in your calculations
without the need to store an extra field anywhere.


To do what you want, I would think you would need some combination of
DateAdd and DateSerial. For example, the following would return the first
day of the month following the person's birthmonth, for the current year....

DateSerial(Year(Date()),Month(BirthDate)+1,1)

I have not tested that,but I think it will work. You can look at the help
file for "DateSerial" if you need more help.

You should be able to take that and figure out the last day of the month six
months later, the first day of the month 7 months later, and the last day of
the birthmonth.

 

[Fred Boer]

Joins a heavy-metal rock band and changes their name to "Satan"?

LOL! Probably better than a 1 in 9,999 chance this *will* happen!

Cheers!
Fred Boer

P.S. Maybe 1 in 666...

 

[Duane Hookom]

Must be Friday in here ;-)

 

[Gary Walter]

Hmm ... "heavy-metal"...

Probably better chance they unwittingly change
their name to "Satin" -- but meant "Satan"