Date of Month vs. Now

Greetings again all,
I think I figured out where I need to go with the Date thing. In my
table tblPilot the Pilots Birthday is listed. His/Her Age is irrelevant,
but we track their hours flown in two six month increments. The increments
are as follows

1st Increment begins the 1st day of the month following the month they were
born in: Example Born 15 Oct Increment 1 date will be 1 Nov through 30
April and Increment two date will be 1 May through 31 October

Pilots fly hours and they are tracked by the above two periods. I need to
be able to tell the database that the start date is either 2004 if their
birthmonth has not passed or 2005 if their birthmonth has passed. So for
example:

Pilot A is born in March so his increment date starts on 1 April. Since 1
April 2005 has arrived, I want his increment 1 start date to be 1 April 2005

Pilot B is born in August so his increment date is 1 September. Since 1
September has not arrived yet for 2005, I want his increment start date to
be 1 Sep 04. and then change to 1 sep 05 once the date reaches 1 sep 05.

The pilots hours restart on his/her New Increment date so I will archive
them with a Active Checkbox and run a query.

My mind says that I should run a query somewhere, as this date needs to be
updated only once a year.
Should I have a field in the table called CurrentMonth and then do an Update
Query or Append Query?

Can I have it do an update query on the Birthday field in the tblPilot or
what is best recommended for this?

Thanks in Advance. Sorry for all the questions about Dates, I don't have
alot of resources over here so I am relying on the only real one I have and
that is this great msnews.micorsoft.com place.

Wally Steadman
US Army in Iraq.

Walter

I don't understand. The example you gave appears to only use a 12-month
period, but your description talked about "six-month increments". How are
these related?

What are you intending to do with the total number of hours flown during a
(to-be-determined) time interval? I ask, as it may affect how you calculate
it, and how, if at all, you store it.

Without further clarification, I only have a vague sense that you are trying
to sum up hours per air-person, for some (?unique/personal) interval(s?)...

Good luck (& thanks!)

Jeff Boyce
Access MVP

"Walter Steadman" wrote in message
...
Greetings again all,
I think I figured out where I need to go with the Date thing. In my
table tblPilot the Pilots Birthday is listed. His/Her Age is irrelevant,
but we track their hours flown in two six month increments. The
increments are as follows

1st Increment begins the 1st day of the month following the month they
were born in: Example Born 15 Oct Increment 1 date will be 1 Nov through
30 April and Increment two date will be 1 May through 31 October

Pilots fly hours and they are tracked by the above two periods. I need to
be able to tell the database that the start date is either 2004 if their
birthmonth has not passed or 2005 if their birthmonth has passed. So for
example:

Pilot A is born in March so his increment date starts on 1 April. Since 1
April 2005 has arrived, I want his increment 1 start date to be 1 April
2005

Pilot B is born in August so his increment date is 1 September. Since 1
September has not arrived yet for 2005, I want his increment start date to
be 1 Sep 04. and then change to 1 sep 05 once the date reaches 1 sep 05.

The pilots hours restart on his/her New Increment date so I will archive
them with a Active Checkbox and run a query.

My mind says that I should run a query somewhere, as this date needs to be
updated only once a year.
Should I have a field in the table called CurrentMonth and then do an
Update Query or Append Query?

Can I have it do an update query on the Birthday field in the tblPilot or
what is best recommended for this?

Thanks in Advance. Sorry for all the questions about Dates, I don't have
alot of resources over here so I am relying on the only real one I have
and that is this great msnews.micorsoft.com place.

Wally Steadman
US Army in Iraq.

Jeff,
Sorry I wasn't very clear. The time duration is for a Year, but they
are looked at in 6 month increments. The reason for the 6 month intervals
is that a pilot must fly so many hours each 6 month period. so based on my
example earlier the report for the pilots would look like this
Using today as the date of the report for example:

Pilot A is born in March so his 1st increment dates are from1 April to 30
September 2005.
Pilot B is born in August so his 1st increment dates are 1 September to 28
February.


Say Pilot A flew the following:
April 5 2005 flew 5 hours
April 7 2005 flew 2 hours

then in his 1st Increment total (which I have made a query to sum totals of
hours during first increment) would be 7 hours and his second would be 0
because we have not reached 1 October which is the start of his 2nd
Increment.

Say Pilot B flew the following:
2 Sep 04 flew 1 hour
4 sep 04 flew 3 hours
2 Oct 04 flew 2 hours
3 Mar 05 flew 2 hour
4 mar 05 flew 2 hours

His first Increment Totals would be 6 hours because he flew 6 hours between
1 Sep 04 and 28 Feb 05 and his 2nd Increment hours would be 4 since he has
so far flown 4 hours between 1 Mar and 31 August.

Their Year starts over on the 1st day of the month after their birth month.
So I will archive those dates accordingly.

A report would look like this:

Pilot ID 1st Increment 2nd Increment Total
A 7 0
7
B 6 4
10

So that is why I think I might need to differentiate between the year and
update it when the last day of the birth month passes.
Thinking if I put a field in the tblPilots called PilotDate and have an
update query set the date based on if today's date is before the first of
the month following their birth month or something like that, but not really
sure of the best approach. I use Yahoo Messenger and AIM as well if it
would be easier to get in a chat and discuss it.

Yahoo name is walter_steadman
AIM is WallySteadman

Thanks for any assistance

Wally Steadman
US Army in Iraq



"Jeff Boyce" wrote in message
...
Walter

I don't understand. The example you gave appears to only use a 12-month
period, but your description talked about "six-month increments". How are
these related?

What are you intending to do with the total number of hours flown during a
(to-be-determined) time interval? I ask, as it may affect how you
calculate it, and how, if at all, you store it.

Without further clarification, I only have a vague sense that you are
trying to sum up hours per air-person, for some (?unique/personal)
interval(s?)...

Good luck (& thanks!)

Jeff Boyce
Access MVP

"Walter Steadman" wrote in
message ...
Greetings again all,
I think I figured out where I need to go with the Date thing. In my
table tblPilot the Pilots Birthday is listed. His/Her Age is irrelevant,
but we track their hours flown in two six month increments. The
increments are as follows

1st Increment begins the 1st day of the month following the month they
were born in: Example Born 15 Oct Increment 1 date will be 1 Nov
through 30 April and Increment two date will be 1 May through 31 October

Pilots fly hours and they are tracked by the above two periods. I need
to be able to tell the database that the start date is either 2004 if
their birthmonth has not passed or 2005 if their birthmonth has passed.
So for example:

Pilot A is born in March so his increment date starts on 1 April. Since
1 April 2005 has arrived, I want his increment 1 start date to be 1 April
2005

Pilot B is born in August so his increment date is 1 September. Since 1
September has not arrived yet for 2005, I want his increment start date
to be 1 Sep 04. and then change to 1 sep 05 once the date reaches 1 sep
05.

The pilots hours restart on his/her New Increment date so I will archive
them with a Active Checkbox and run a query.

My mind says that I should run a query somewhere, as this date needs to
be updated only once a year.
Should I have a field in the table called CurrentMonth and then do an
Update Query or Append Query?

Can I have it do an update query on the Birthday field in the tblPilot or
what is best recommended for this?

Thanks in Advance. Sorry for all the questions about Dates, I don't have
alot of resources over here so I am relying on the only real one I have
and that is this great msnews.micorsoft.com place.

Wally Steadman
US Army in Iraq.

Walter

Thanks for the clarifications.

When I look at the report you'd like to prepare, I don't see a "date". Is
this report supposed to cover a particular year (e.g., year ending
12/31/2005)?

And how do you want to handle the "boundary conditions"? That is, how
should a Pilot be counted if s/he has a DOB of 12/31/xxxx? Of 1/1/yyyy?
Which "start date" would you use for these?

Thanks for the additional info...

Jeff Boyce
Access MVP

Jeff,
The date is relevant for each pilot in the following way:
Say Pilot A's Birthday is in June, so if I open the Database today( 8 Jun
05), because the end of June has not yet arrived and the pilots new year for
counting hours does not start until the 1st day after the month they were
born, I need it to base its calculations on 1 July 04 through 30 June 05,
but when I open the database on 4 July, since it has already passed the last
day of Pilot A's birth month, then I will need it to look at pilot A's year
as 1 July 05 through 30 Jun 06 (I will have it archive the records from the
old year). I will use calculations in queries and reports to pull out the
relevant increment times (as mentioned with the two 6 month increments in
earlier posts).

So say when I add Pilot A to the system, I put his birthdate as 15 June
1966. Then I need it to ask if the Birthmonth has passed, if it has passed
then I need it to make the date 15 June 05 (or current year) but if it
hasn't passed, then I need it to make the date 15 June 04 (the last year).
That way when I run my calculations (queries, unbound text boxes in reports)
I can use the birthmonth as an anchor point.

That is why I was thinking it should be some type of event that is checked
when I open the database. But the date must be available to use in queries
and such. That was why I was thinking an update query of some sort. It
will look at the birthmonth for all pilots (hence a query).

If I am confusing you still, please let me know. I can send you the
structure of my Database with some sample data and some information about
the data.

TIA

Wally Steadman
US Army in Iraq



"Jeff Boyce" wrote in message
...
Walter

Thanks for the clarifications.

When I look at the report you'd like to prepare, I don't see a "date". Is
this report supposed to cover a particular year (e.g., year ending
12/31/2005)?

And how do you want to handle the "boundary conditions"? That is, how
should a Pilot be counted if s/he has a DOB of 12/31/xxxx? Of 1/1/yyyy?
Which "start date" would you use for these?

Thanks for the additional info...

Jeff Boyce
Access MVP

Walter

OK, I think I have a clearer picture now.

I would suggest, first, that you don't add fields to your tables if the
values can be calculated. Your "increment" values are calculated, based on
a birthmonth and an "as of" date (it sounds like you want to use "today's"
date, and you could make this even more generic and generate your increments
reports for any point in time).

I'll dig into it a bit more and post back -- right now my thoughts are
running to the use of the Month() function to compare MonthOfADate to
MonthAfterBirthMonth (I made these up, as calculated "values" in a query
that's pulling in Date() and PilotDOB). After selecting the records (flight
time) meeting the previous criteria, I would create two "increments" and sum
the hours in each increment, for each Pilot.

As a matter of clarification, if two pilots had two differing months of
birth, their two increments would represent differing time periods (still
totaling a year, but starting/stopping at different points). Is that
correct?

Regards

Jeff Boyce
Access MVP

"Walter Steadman" wrote in message
...
Jeff,
The date is relevant for each pilot in the following way:
Say Pilot A's Birthday is in June, so if I open the Database today( 8 Jun
05), because the end of June has not yet arrived and the pilots new year
for
counting hours does not start until the 1st day after the month they were
born, I need it to base its calculations on 1 July 04 through 30 June 05,
but when I open the database on 4 July, since it has already passed the
last
day of Pilot A's birth month, then I will need it to look at pilot A's
year
as 1 July 05 through 30 Jun 06 (I will have it archive the records from
the
old year). I will use calculations in queries and reports to pull out the
relevant increment times (as mentioned with the two 6 month increments in
earlier posts).

So say when I add Pilot A to the system, I put his birthdate as 15 June
1966. Then I need it to ask if the Birthmonth has passed, if it has
passed
then I need it to make the date 15 June 05 (or current year) but if it
hasn't passed, then I need it to make the date 15 June 04 (the last year).
That way when I run my calculations (queries, unbound text boxes in
reports)
I can use the birthmonth as an anchor point.

That is why I was thinking it should be some type of event that is checked
when I open the database. But the date must be available to use in
queries
and such. That was why I was thinking an update query of some sort. It
will look at the birthmonth for all pilots (hence a query).

If I am confusing you still, please let me know. I can send you the
structure of my Database with some sample data and some information about
the data.

TIA

Wally Steadman
US Army in Iraq

Jeff,
That is correct. And as you said, it looks at the date that the DB is
opened on, so today it looks at 6/8/05 and tomorrow it looks at 6/9/05 but I
think you see where I am going with it. Yeah I don't want to store
unnecessary items in a table either, just haven't figured out the details.
Can I write a function or something? Or is a query the best way to go.
Again, I appreciate all the help

Wally


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Walter

OK, I think I have a clearer picture now.

I would suggest, first, that you don't add fields to your tables if the
values can be calculated. Your "increment" values are calculated, based
on
a birthmonth and an "as of" date (it sounds like you want to use "today's"
date, and you could make this even more generic and generate your
increments
reports for any point in time).

I'll dig into it a bit more and post back -- right now my thoughts are
running to the use of the Month() function to compare MonthOfADate to
MonthAfterBirthMonth (I made these up, as calculated "values" in a query
that's pulling in Date() and PilotDOB). After selecting the records
(flight
time) meeting the previous criteria, I would create two "increments" and
sum
the hours in each increment, for each Pilot.

As a matter of clarification, if two pilots had two differing months of
birth, their two increments would represent differing time periods (still
totaling a year, but starting/stopping at different points). Is that
correct?

Regards

Jeff Boyce
Access MVP

"Walter Steadman" wrote in
message
...
Jeff,
The date is relevant for each pilot in the following way:
Say Pilot A's Birthday is in June, so if I open the Database today( 8 Jun
05), because the end of June has not yet arrived and the pilots new year
for
counting hours does not start until the 1st day after the month they were
born, I need it to base its calculations on 1 July 04 through 30 June 05,
but when I open the database on 4 July, since it has already passed the
last
day of Pilot A's birth month, then I will need it to look at pilot A's
year
as 1 July 05 through 30 Jun 06 (I will have it archive the records from
the
old year). I will use calculations in queries and reports to pull out
the
relevant increment times (as mentioned with the two 6 month increments in
earlier posts).

So say when I add Pilot A to the system, I put his birthdate as 15 June
1966. Then I need it to ask if the Birthmonth has passed, if it has
passed
then I need it to make the date 15 June 05 (or current year) but if it
hasn't passed, then I need it to make the date 15 June 04 (the last
year).
That way when I run my calculations (queries, unbound text boxes in
reports)
I can use the birthmonth as an anchor point.

That is why I was thinking it should be some type of event that is
checked
when I open the database. But the date must be available to use in
queries
and such. That was why I was thinking an update query of some sort. It
will look at the birthmonth for all pilots (hence a query).

If I am confusing you still, please let me know. I can send you the
structure of my Database with some sample data and some information about
the data.

TIA

Wally Steadman
US Army in Iraq

Wally

I've got a stripped-down example db that I'm testing in. I'll work on this
a bit more and then respond. There is one other thing (isn't there
always!)...

I am assuming the structure of your data is something like:

tblPilot
PilotID
PilotDOB
...

trelPilotHours
PilotHoursID
PilotID
FlightDateTime (to allow for more than one flight on one day)
Hours
...

This is what I'm basing the query-approach on. Does this match your
structure?

Regards

Jeff Boyce
Access MVP


"Walter Steadman" wrote in message
...
Jeff,
That is correct. And as you said, it looks at the date that the DB is
opened on, so today it looks at 6/8/05 and tomorrow it looks at 6/9/05 but
I
think you see where I am going with it. Yeah I don't want to store
unnecessary items in a table either, just haven't figured out the details.
Can I write a function or something? Or is a query the best way to go.
Again, I appreciate all the help

Wally

Jeff, yes it matches great. I don't do the time portion, but I do add
multiple entries for a pilot on a given day.
There are other fields in each table but for the questions that we are
working on, those fields will do. The other fileds are mostly just
informational

"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Wally

I've got a stripped-down example db that I'm testing in. I'll work on
this
a bit more and then respond. There is one other thing (isn't there
always!)...

I am assuming the structure of your data is something like:

tblPilot
PilotID
PilotDOB
...

trelPilotHours
PilotHoursID
PilotID
FlightDateTime (to allow for more than one flight on one day)
Hours
...

This is what I'm basing the query-approach on. Does this match your
structure?

Regards

Jeff Boyce
Access MVP


"Walter Steadman" wrote in
message
...
Jeff,
That is correct. And as you said, it looks at the date that the DB
is
opened on, so today it looks at 6/8/05 and tomorrow it looks at 6/9/05
but
I
think you see where I am going with it. Yeah I don't want to store
unnecessary items in a table either, just haven't figured out the
details.
Can I write a function or something? Or is a query the best way to go.
Again, I appreciate all the help

Wally

Just in case you wanted them Jeff, below are the fields in the tables

tblPilot cont
PilotID
LastName
FirstName
Grade
Duty
FACLevel
BirthDate

tblHours contains
HourID
PilotID
Date
TailNumber
DutyID
MissionID
FlightStatus
HoursFlown


Wally

dman" wrote in message
...
Jeff, yes it matches great. I don't do the time portion, but I do add
multiple entries for a pilot on a given day.
There are other fields in each table but for the questions that we are
working on, those fields will do. The other fileds are mostly just
informational

"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Wally

I've got a stripped-down example db that I'm testing in. I'll work on
this
a bit more and then respond. There is one other thing (isn't there
always!)...

I am assuming the structure of your data is something like:

tblPilot
PilotID
PilotDOB
...

trelPilotHours
PilotHoursID
PilotID
FlightDateTime (to allow for more than one flight on one day)
Hours
...

This is what I'm basing the query-approach on. Does this match your
structure?

Regards

Jeff Boyce
Access MVP


"Walter Steadman" wrote in
message
...
Jeff,
That is correct. And as you said, it looks at the date that the DB
is
opened on, so today it looks at 6/8/05 and tomorrow it looks at 6/9/05
but
I
think you see where I am going with it. Yeah I don't want to store
unnecessary items in a table either, just haven't figured out the
details.
Can I write a function or something? Or is a query the best way to go.
Again, I appreciate all the help

Wally