Rolling 7, 28, 30 and 365 day totals

  • Thread starter Thread starter Ant
  • Start date Start date
A

Ant

Hi,

I work in the aviation industry, and we are required to keep track of our
Flight and Duty Hours. A part of this requirement is that we show 7 day, 28
day (or 30 day depending on employer) and 365 day totals and these totals
guide us to how far off we are from our flying limits.

I have been able to create the basic calculation in a Query using "DateAdd",
but it only outputs a single figure for the current day.

What I am after looks something like this (imagine an excel spreadshee) :

Date Day Hrs Ngt Hrs Total Hr 7day 28day 365day
19/12/08 2.3 0.0 2.3 2.3 2.3 2.3
20/12/08 4.1 1.2 5.3 7.6 7.6 7.6
21/12/08 0.0 0.0 0.0 0.0 0.0 0.0
22/12/08 1.2 0.0 1.2 8.8 8.8 8.8
23/12/08 1.0 3.0 4.0 12.8 12.8 12.8

The above assumes that the 19/12/08 was the start of any flying for inexcess
of one year, so no rolling totals have been carried over.

I want to show the above "spreadsheet" style in both Report and Form, and
zero flying days must be shown as an entry, even though there may be no entry
for it (is this possible??)

Ideally, (but I imagine it may not be possible), I would like to select a
date range, say 1 Nov 08 to 30 Nov 08, which will have 30 lines (days) in the
report, but the rolling totals don't start calculating as at 1 Nov 08, it
must be able to bring the rolling totals in from prior to the start date of
the report.

Anybody able to help?? I can post more info to help.

Thanking you in advance
Ant
 
Use a subquery to give you the sum of hours for each of the periods.

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Here's a simplified example of the kind of thing you would type into the
Field row in query design to get the Hrs of flights that took off in the
previous 7 days:
(SELECT Sum(Hrs) AS HowMany
FROM Table1 AS Dupe
WHERE Dupe.TakeOffDateTime
Between Table1.TakeOffDateTime -7
And Table1.TakeOffDateTime)

The reality is that this is complicated quite a bit by timezones, durations,
and so on, but that's the process.
 
Ant,

This is a several step process.

1. In order to get every day in your report you need a query or table that
contains all the days between two dates. To accomplish this, I have a
table (tbl_Numbers) that contains a single field (lngNumber), with 10
records (0-9). I then create a query (qry_Numbers) which looks like:

SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

When run, this will give you the numbers from 0 to 999. If you are only
going to run this report for 30 days at a time, you might want to just drop
the Hundreds table from the query to give you a smaller data set, and
quicker run time.

2. Now you need to create another query (qry_Dates) that looks something
like the folllowing. This will give you a result set with all the dates
between your start and end dates.

SELECT DateAdd("d", lngNumber, [Forms]!yourForm.txtStartDate) as [FltDate]
FROM qry_Numbers
WHERE DateAdd("d", lngNumber, [Forms]!yourForm.txtStartDate) <=
Forms!yourForm.txtEndDate

3. Finally, create another query to get your flight totals:

SELECT D.FltDate,
Sum(IIF(T.FltDate = D.FltDate, NZ([DayHours],0), 0)) as [Day Hrs],
Sum(IIF(T.FltDate = D.FltDate, NZ([NightHours], 0), 0)) as [Ngt Hrs],
Sum(IIF(T.FltDate = D.FltDate, NZ([DayHours],0) + NZ([NightHours],0),0)) as
[Total Hr],
Sum(IIF(T.FltDate >= DateAdd("d", -7, D.FltDate), NZ([DayHours], 0) +
NZ([NightHours], 0), 0)) as [7 day],
Sum(IIF(T.FltDate >= DateAdd("d", -28, D.FltDate), NZ([DayHours], 0) +
NZ([NightHours], 0), 0)) as [28 day],
Sum(IIF(T.FltDate >= DateAdd("d", -365, D.FltDate), NZ([DayHours], 0) +
NZ([NightHours], 0), 0)) as [365 day],
FROM qry_Dates as D, YourTable as T
GROUP BY D.FltDate

You may need to play with the DateAdd values and equalities to make sure you
get 7, 28, and 365, this may actually give you 8, 29, and 366.

You will notice that I used the NZ( ) function in each of these
calculations, to avoid the case where either the DayHours or NightHours
fields is left blank (NULL)

HTH
Dale
 
Thanks Allen for your response.

I will work through your example tonight when I return home.

Time zones are not relevant in our calculations for flight times, as we are
not recording the start and end times of the flight, we are just recording
the total duration of the flight.

Though it could become an issue when recording Duty time, most operators
(including us) use the rule that the time zone you started the day in,
applies for the rest of the day.

Regards
Ant
 
Hi Dale,

Thanks for your response. It looks as complicated as I thought, but I will
attempt it tonight.

Hope you don't mind if I ask you more questions after I tried it.

Regards
Ant


Dale Fye said:
Ant,

This is a several step process.

1. In order to get every day in your report you need a query or table that
contains all the days between two dates. To accomplish this, I have a
table (tbl_Numbers) that contains a single field (lngNumber), with 10
records (0-9). I then create a query (qry_Numbers) which looks like:

SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

When run, this will give you the numbers from 0 to 999. If you are only
going to run this report for 30 days at a time, you might want to just drop
the Hundreds table from the query to give you a smaller data set, and
quicker run time.

2. Now you need to create another query (qry_Dates) that looks something
like the folllowing. This will give you a result set with all the dates
between your start and end dates.

SELECT DateAdd("d", lngNumber, [Forms]!yourForm.txtStartDate) as [FltDate]
FROM qry_Numbers
WHERE DateAdd("d", lngNumber, [Forms]!yourForm.txtStartDate) <=
Forms!yourForm.txtEndDate

3. Finally, create another query to get your flight totals:

SELECT D.FltDate,
Sum(IIF(T.FltDate = D.FltDate, NZ([DayHours],0), 0)) as [Day Hrs],
Sum(IIF(T.FltDate = D.FltDate, NZ([NightHours], 0), 0)) as [Ngt Hrs],
Sum(IIF(T.FltDate = D.FltDate, NZ([DayHours],0) + NZ([NightHours],0),0)) as
[Total Hr],
Sum(IIF(T.FltDate >= DateAdd("d", -7, D.FltDate), NZ([DayHours], 0) +
NZ([NightHours], 0), 0)) as [7 day],
Sum(IIF(T.FltDate >= DateAdd("d", -28, D.FltDate), NZ([DayHours], 0) +
NZ([NightHours], 0), 0)) as [28 day],
Sum(IIF(T.FltDate >= DateAdd("d", -365, D.FltDate), NZ([DayHours], 0) +
NZ([NightHours], 0), 0)) as [365 day],
FROM qry_Dates as D, YourTable as T
GROUP BY D.FltDate

You may need to play with the DateAdd values and equalities to make sure you
get 7, 28, and 365, this may actually give you 8, 29, and 366.

You will notice that I used the NZ( ) function in each of these
calculations, to avoid the case where either the DayHours or NightHours
fields is left blank (NULL)

HTH
Dale

Ant said:
Hi,

I work in the aviation industry, and we are required to keep track of our
Flight and Duty Hours. A part of this requirement is that we show 7 day,
28
day (or 30 day depending on employer) and 365 day totals and these totals
guide us to how far off we are from our flying limits.

I have been able to create the basic calculation in a Query using
"DateAdd",
but it only outputs a single figure for the current day.

What I am after looks something like this (imagine an excel spreadshee) :

Date Day Hrs Ngt Hrs Total Hr 7day 28day 365day
19/12/08 2.3 0.0 2.3 2.3 2.3 2.3
20/12/08 4.1 1.2 5.3 7.6 7.6 7.6
21/12/08 0.0 0.0 0.0 0.0 0.0 0.0
22/12/08 1.2 0.0 1.2 8.8 8.8 8.8
23/12/08 1.0 3.0 4.0 12.8 12.8 12.8

The above assumes that the 19/12/08 was the start of any flying for
inexcess
of one year, so no rolling totals have been carried over.

I want to show the above "spreadsheet" style in both Report and Form, and
zero flying days must be shown as an entry, even though there may be no
entry
for it (is this possible??)

Ideally, (but I imagine it may not be possible), I would like to select a
date range, say 1 Nov 08 to 30 Nov 08, which will have 30 lines (days) in
the
report, but the rolling totals don't start calculating as at 1 Nov 08, it
must be able to bring the rolling totals in from prior to the start date
of
the report.

Anybody able to help?? I can post more info to help.

Thanking you in advance
Ant
Click to expand...
Click to expand...
 
Back
Top