Date of Month vs. Now

[Walter Steadman]

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 Boyce]

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]

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]

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 Steadman]

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]

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]

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]

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]

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

 

[Walter Steadman]

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

 

[Jeff Boyce]

Wally

Here's a SQL statement -- create a new empty query, select the SQL view, and
paste this statement in (it will only work if the field and table names line
up -- see below).

SELECT tblP.P_ID, tblP.P_DOB, ((Month([P_DOB])) Mod 12)+1 AS
MonthAfterBirth, DateSerial(Year([Enter Report AsOf
Date]),[MonthAfterBirth],1) AS I1, IIf([I1]<[Enter Report AsOf
Date],DateAdd("m",-6,[I1]),[I1]) AS Incr1, DateAdd("m",6,[Incr1]) AS Incr2
FROM tblP;

Please consider this a "proof of concept" only. You'll want to confirm that
it handles the data you have, in the manner you need it to. Note that I've
used a table (tblP) with two fields (P_ID, P_DOB) as the first step.

You can create (make table query) a similar test table, or modify these
table and field names to match your database for testing.

Note also that I used "temporary" fields in the query to build intermediate
values that other fields use. You could further simplify the query by
embedding the calculation of those intermediate values directly into the
final fields you're after. I used the temporary fields to make the
logic/flow a little clearer.

You will also notice that I've added a "parameter" field for the comparison
date. If you will ONLY use today's date and NEVER EVER want a report as of
an alternative date, you can replace this parameter with the expression
"Date()" (without the quotes). If you keep the parameter, just enter the
"AsOf" date (including today's date) to do the calculation as of that date.

Finally, please note that this only handles the calculation of Begin Dates
for Increments1 & 2. You can build one or more additional queries, using
this first query linked to the "hours" table to help: 1) select the date
ranges of hours flown, and 2) sum up those hours.

I suspect you'll want to use something like a cross-tabs query, and you may
be able to shortcut some of the above steps by using only the Increment1
start date and grouping by time intervals, either in the query or in the
subsequent report (I haven't checked this out).

Best of luck

Jeff Boyce
<Access MVP>