Date Question, not sure which forum

W

Walter Steadman

Greetings all,
I have built a DB that tracks pilot hours and it is working (but very
clunky, and bad design). Here is where I am stuck.

Pilots hours are tracked over a 1 year time but in two six month blocks.
Block one starts the first day of the month following the birth month and
runs for 6 months. Second block starts when the first block ends and runs
through the last day of the birth month.
Example:
Name Birthdate 1st Semi Start 1st Semi End 2nd
Semi Start 2nd Semi End
John 15 Feb 66 1 March 31 August 1
September 28 February
Since March 1st has already passed (today being 29 June 05), it would be 1
March 05, 31 Aug 05, 1 Sep 05, 28 Feb 06

Dan 14 Oct 66 1 November 30 April 1
May 31 Oct
Since March has not passed yet it would be 1 November 04, 30 April 05, 1 May
05, 31 Oct 05

Notice how each pilots year is different based on Birth Month. At the start
of (1st Semi) pilots time goes back to 0

Example:
John and Dan both fly the following days.
15 Dec 04 4hrs
20 Dec 04 3hrs
13 Mar 05 1hrs
15 Mar 05 2hrs
10 Jun 05 5hrs
17 Jun 05 6hrs

When I do a total for each Semi it would look like this:

Name 1st Semi 2nd Semi
John 14 0
Dan 10 11

Noting that John's December hours do not count because they were from his
last years report.

How I am doing it now is by adding a field to my table called "startdate"
and then doing a few Append queries that make the start date either the (1st
Semi date using this year) if the 1st Semi Date has already passed or I make
the 1st Semi date using last year if their 1st Semi Date has not passed yet.
I know this is wrong, but I am stumped as to how to get these dates fluidly
while only inputting the Birthdate in the table. Should I get these dates
through a query? In a report? I am using the DateSerial Function and an
IIF statement to get the start date currently. I have added the code to my
Append Queries along with a list of the table names. This has been baffling
me for a while now and I would appreciate any advice on how to tackle this
issue.

TABLE: tblPilot
PilotID (PK)
LastName
FirstName
Birthdate mm,dd,yyyy

TABLE tblHours
HourID (AutoNumber, PK)
PilotID (FK to tblPilot)
DateFlew mm, dd, yyyy
HoursFlown Number field
Active Yes/No
1st Semi Yes/No

When the first form opens it contains the following code (OnOpen):

Private Sub Form_Open(Cancel As Integer)
CurrentDb.Execute "uqryStartDateCurrYear", dbFailOnError
CurrentDb.Execute "uqrySetStartDate", dbFailOnError
CurrentDb.Execute "uqryActive", dbFailOnError
CurrentDb.Execute "uqryActiveHours", dbFailOnError
CurrentDb.Execute "uqry1stsemiFalse", dbFailOnError
CurrentDb.Execute "uqry1stSemi", dbFailOnError
End Sub


I have listed the SQL for each above query below.

"uqryStartDateCurrYear"
UPDATE tblPilot SET tblPilot.StartDate =
DateSerial(Year(Date()),Month([birthmonth]),Day([birthmonth]));

"uqrySetStartDate"
UPDATE tblPilot SET tblPilot.StartDate =
DateSerial(Year(Date())-1,Month([birthmonth]),Day([birthmonth]))
WHERE (((Date())<DateSerial(Year(Date()),Month([birthmonth])+1,1)));

uqryActive
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
SET tblHours.Active = False;

uqryActiveHours
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
SET tblHours.Active = True
WHERE
(((tblHours.flyDate)>DateSerial(Year([startdate]),Month([startdate])+1,0)));

uqry1stsemiFalse
UPDATE tblHours SET tblHours.[1stSemi] = False;

uqry1stSemi
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
SET tblHours.[1stSemi] = True
WHERE (((tblHours.flyDate) Between
DateSerial(Year([startdate]),Month([startdate])+1,0) And
DateSerial(Year([startdate]),Month([startdate])+7,1)));


Again, I know this is wrong and clunky as I am storing alot of unnecessary
data such as Active (Although it has other uses I think) and 1st Semi (I
only have that field to say If True/False you will be part of which Semi.

Any advice on how to accomplish this less WRONG would sincerely be
appreciated.

If this is posted to the wrong NewsGroup, I apologize.

TIA
 
S

sanfu

Walter Steadman said:
Greetings all,
I have built a DB that tracks pilot hours and it is working (but very
clunky, and bad design). Here is where I am stuck.

Pilots hours are tracked over a 1 year time but in two six month blocks.
Block one starts the first day of the month following the birth month and
runs for 6 months. Second block starts when the first block ends and runs
through the last day of the birth month.
Example:
Name Birthdate 1st Semi Start 1st Semi End 2nd
Semi Start 2nd Semi End
John 15 Feb 66 1 March 31 August 1
September 28 February
Since March 1st has already passed (today being 29 June 05), it would be 1
March 05, 31 Aug 05, 1 Sep 05, 28 Feb 06

Dan 14 Oct 66 1 November 30 April 1
May 31 Oct
Since March has not passed yet it would be 1 November 04, 30 April 05, 1 May
05, 31 Oct 05

Notice how each pilots year is different based on Birth Month. At the start
of (1st Semi) pilots time goes back to 0

Example:
John and Dan both fly the following days.
15 Dec 04 4hrs
20 Dec 04 3hrs
13 Mar 05 1hrs
15 Mar 05 2hrs
10 Jun 05 5hrs
17 Jun 05 6hrs

When I do a total for each Semi it would look like this:

Name 1st Semi 2nd Semi
John 14 0
Dan 10 11

Noting that John's December hours do not count because they were from his
last years report.

How I am doing it now is by adding a field to my table called "startdate"
and then doing a few Append queries that make the start date either the (1st
Semi date using this year) if the 1st Semi Date has already passed or I make
the 1st Semi date using last year if their 1st Semi Date has not passed yet.
I know this is wrong, but I am stumped as to how to get these dates fluidly
while only inputting the Birthdate in the table. Should I get these dates
through a query? In a report? I am using the DateSerial Function and an
IIF statement to get the start date currently. I have added the code to my
Append Queries along with a list of the table names. This has been baffling
me for a while now and I would appreciate any advice on how to tackle this
issue.

TABLE: tblPilot
PilotID (PK)
LastName
FirstName
Birthdate mm,dd,yyyy

TABLE tblHours
HourID (AutoNumber, PK)
PilotID (FK to tblPilot)
DateFlew mm, dd, yyyy
HoursFlown Number field
Active Yes/No
1st Semi Yes/No

When the first form opens it contains the following code (OnOpen):

Private Sub Form_Open(Cancel As Integer)
CurrentDb.Execute "uqryStartDateCurrYear", dbFailOnError
CurrentDb.Execute "uqrySetStartDate", dbFailOnError
CurrentDb.Execute "uqryActive", dbFailOnError
CurrentDb.Execute "uqryActiveHours", dbFailOnError
CurrentDb.Execute "uqry1stsemiFalse", dbFailOnError
CurrentDb.Execute "uqry1stSemi", dbFailOnError
End Sub


I have listed the SQL for each above query below.

"uqryStartDateCurrYear"
UPDATE tblPilot SET tblPilot.StartDate =
DateSerial(Year(Date()),Month([birthmonth]),Day([birthmonth]));

"uqrySetStartDate"
UPDATE tblPilot SET tblPilot.StartDate =
DateSerial(Year(Date())-1,Month([birthmonth]),Day([birthmonth]))
WHERE (((Date())<DateSerial(Year(Date()),Month([birthmonth])+1,1)));

uqryActive
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
SET tblHours.Active = False;

uqryActiveHours
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
SET tblHours.Active = True
WHERE
(((tblHours.flyDate)>DateSerial(Year([startdate]),Month([startdate])+1,0)));

uqry1stsemiFalse
UPDATE tblHours SET tblHours.[1stSemi] = False;

uqry1stSemi
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
SET tblHours.[1stSemi] = True
WHERE (((tblHours.flyDate) Between
DateSerial(Year([startdate]),Month([startdate])+1,0) And
DateSerial(Year([startdate]),Month([startdate])+7,1)));


Again, I know this is wrong and clunky as I am storing alot of unnecessary
data such as Active (Although it has other uses I think) and 1st Semi (I
only have that field to say If True/False you will be part of which Semi.

Any advice on how to accomplish this less WRONG would sincerely be
appreciated.

If this is posted to the wrong NewsGroup, I apologize.

TIA
Click to expand...

Here is a query that seems to return the results you want. Given the small
number of data points, you should test this with more of your data. You could
also write functions to return the values.

I used these two tables/fields:

TABLE: tblPilot
PilotID (PK)
LastName
FirstName
Birthdate mm,dd,yyyy

TABLE tblHours
HourID (AutoNumber, PK)
PilotID (FK to tblPilot)
DateFlew mm, dd, yyyy
HoursFlown Number field


and entered the 6 data points for each flier

-----
Create a new query and switch to SQL view. Paste in the following, then save
the query:

SELECT tblHours.PilotID, tblPilot.LastName, tblPilot.FirstName,
Sum(IIf([dateflew]>DateSerial(Year(Date())-IIf(Month([Birthdate])>Month(Date()),1,0),Month([Birthdate])+1,1)
And
[dateflew]<DateAdd("m",6,DateSerial(Year(Date())-IIf(Month([Birthdate])>Month(Date()),1,0),Month([Birthdate])+1,1))-1,[hoursflown],0))
AS 1stSemiHrs,
Sum(IIf([dateflew]>DateAdd("m",6,DateSerial(Year(Date())-IIf(Month([Birthdate])>Month(Date()),1,0),Month([Birthdate])+1,1))
And
[dateflew]<DateAdd("m",12,DateSerial(Year(Date())-IIf(Month([Birthdate])>Month(Date()),1,0),Month([Birthdate])+1,1))-1,[hoursflown],0)) AS 2ndSemiHrs
FROM tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
GROUP BY tblHours.PilotID, tblPilot.LastName, tblPilot.FirstName;


Use this query as the record source for a report.

Please note that this query uses *Today's Date*; if you want to be able to
use any date, you will need to create a form and put an unbound text box and
a button on it. Then you would need to replace all occurances of "Date()"
with

[Forms]![FormName]![ControlName]

replacing "FormName" with your form name and "ControlName" with your control
name.

Add code to the button to open either the query or the report.

Enter a date in the text box and click the button.... ta da!!

If you have [problems, post back.

HTH
 
W

Walter Steadman

Thank you so much. I did not realize you could do iif statements in the SQL
code. I will play with this as it does exactly what I needed it to do and
now I will be able to do even more. Thank you so much.

Wally Steadman


sanfu said:
Walter Steadman said:
Greetings all,
I have built a DB that tracks pilot hours and it is working (but very
clunky, and bad design). Here is where I am stuck.

Pilots hours are tracked over a 1 year time but in two six month blocks.
Block one starts the first day of the month following the birth month and
runs for 6 months. Second block starts when the first block ends and
runs
through the last day of the birth month.
Example:
Name Birthdate 1st Semi Start 1st Semi End 2nd
Semi Start 2nd Semi End
John 15 Feb 66 1 March 31 August
1
September 28 February
Since March 1st has already passed (today being 29 June 05), it would be
1
March 05, 31 Aug 05, 1 Sep 05, 28 Feb 06

Dan 14 Oct 66 1 November 30 April
1
May 31 Oct
Since March has not passed yet it would be 1 November 04, 30 April 05, 1
May
05, 31 Oct 05

Notice how each pilots year is different based on Birth Month. At the
start
of (1st Semi) pilots time goes back to 0

Example:
John and Dan both fly the following days.
15 Dec 04 4hrs
20 Dec 04 3hrs
13 Mar 05 1hrs
15 Mar 05 2hrs
10 Jun 05 5hrs
17 Jun 05 6hrs

When I do a total for each Semi it would look like this:

Name 1st Semi 2nd Semi
John 14 0
Dan 10 11

Noting that John's December hours do not count because they were from his
last years report.

How I am doing it now is by adding a field to my table called "startdate"
and then doing a few Append queries that make the start date either the
(1st
Semi date using this year) if the 1st Semi Date has already passed or I
make
the 1st Semi date using last year if their 1st Semi Date has not passed
yet.
I know this is wrong, but I am stumped as to how to get these dates
fluidly
while only inputting the Birthdate in the table. Should I get these
dates
through a query? In a report? I am using the DateSerial Function and an
IIF statement to get the start date currently. I have added the code to
my
Append Queries along with a list of the table names. This has been
baffling
me for a while now and I would appreciate any advice on how to tackle
this
issue.

TABLE: tblPilot
PilotID (PK)
LastName
FirstName
Birthdate mm,dd,yyyy

TABLE tblHours
HourID (AutoNumber, PK)
PilotID (FK to tblPilot)
DateFlew mm, dd, yyyy
HoursFlown Number field
Active Yes/No
1st Semi Yes/No

When the first form opens it contains the following code (OnOpen):

Private Sub Form_Open(Cancel As Integer)
CurrentDb.Execute "uqryStartDateCurrYear", dbFailOnError
CurrentDb.Execute "uqrySetStartDate", dbFailOnError
CurrentDb.Execute "uqryActive", dbFailOnError
CurrentDb.Execute "uqryActiveHours", dbFailOnError
CurrentDb.Execute "uqry1stsemiFalse", dbFailOnError
CurrentDb.Execute "uqry1stSemi", dbFailOnError
End Sub


I have listed the SQL for each above query below.

"uqryStartDateCurrYear"
UPDATE tblPilot SET tblPilot.StartDate =
DateSerial(Year(Date()),Month([birthmonth]),Day([birthmonth]));

"uqrySetStartDate"
UPDATE tblPilot SET tblPilot.StartDate =
DateSerial(Year(Date())-1,Month([birthmonth]),Day([birthmonth]))
WHERE (((Date())<DateSerial(Year(Date()),Month([birthmonth])+1,1)));

uqryActive
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID =
tblHours.PilotID
SET tblHours.Active = False;

uqryActiveHours
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID =
tblHours.PilotID
SET tblHours.Active = True
WHERE
(((tblHours.flyDate)>DateSerial(Year([startdate]),Month([startdate])+1,0)));

uqry1stsemiFalse
UPDATE tblHours SET tblHours.[1stSemi] = False;

uqry1stSemi
UPDATE tblPilot INNER JOIN tblHours ON tblPilot.PilotID =
tblHours.PilotID
SET tblHours.[1stSemi] = True
WHERE (((tblHours.flyDate) Between
DateSerial(Year([startdate]),Month([startdate])+1,0) And
DateSerial(Year([startdate]),Month([startdate])+7,1)));


Again, I know this is wrong and clunky as I am storing alot of
unnecessary
data such as Active (Although it has other uses I think) and 1st Semi (I
only have that field to say If True/False you will be part of which Semi.

Any advice on how to accomplish this less WRONG would sincerely be
appreciated.

If this is posted to the wrong NewsGroup, I apologize.

TIA
Click to expand...

Here is a query that seems to return the results you want. Given the small
number of data points, you should test this with more of your data. You
could
also write functions to return the values.

I used these two tables/fields:

TABLE: tblPilot
PilotID (PK)
LastName
FirstName
Birthdate mm,dd,yyyy

TABLE tblHours
HourID (AutoNumber, PK)
PilotID (FK to tblPilot)
DateFlew mm, dd, yyyy
HoursFlown Number field


and entered the 6 data points for each flier

-----
Create a new query and switch to SQL view. Paste in the following, then
save
the query:

SELECT tblHours.PilotID, tblPilot.LastName, tblPilot.FirstName,
Sum(IIf([dateflew]>DateSerial(Year(Date())-IIf(Month([Birthdate])>Month(Date()),1,0),Month([Birthdate])+1,1)
And
[dateflew]<DateAdd("m",6,DateSerial(Year(Date())-IIf(Month([Birthdate])>Month(Date()),1,0),Month([Birthdate])+1,1))-1,[hoursflown],0))
AS 1stSemiHrs,
Sum(IIf([dateflew]>DateAdd("m",6,DateSerial(Year(Date())-IIf(Month([Birthdate])>Month(Date()),1,0),Month([Birthdate])+1,1))
And
[dateflew]<DateAdd("m",12,DateSerial(Year(Date())-IIf(Month([Birthdate])>Month(Date()),1,0),Month([Birthdate])+1,1))-1,[hoursflown],0))
AS 2ndSemiHrs
FROM tblPilot INNER JOIN tblHours ON tblPilot.PilotID = tblHours.PilotID
GROUP BY tblHours.PilotID, tblPilot.LastName, tblPilot.FirstName;


Use this query as the record source for a report.

Please note that this query uses *Today's Date*; if you want to be able to
use any date, you will need to create a form and put an unbound text box
and
a button on it. Then you would need to replace all occurances of "Date()"
with

[Forms]![FormName]![ControlName]

replacing "FormName" with your form name and "ControlName" with your
control
name.

Add code to the button to open either the query or the report.

Enter a date in the text box and click the button.... ta da!!

If you have [problems, post back.

HTH
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Type Question 3
Do I need to put any type of pause in the code below? 1
Date of Month vs. Now 10

Top