Finding the highest 7 day total in a pilot's logbook

  • Thread starter Thread starter Ed Finley
  • Start date Start date
E

Ed Finley

I'm looking for a way to search a table that is is basicaly [FlightTime],
[FlightDate] for the highest total time in any 7 day period. In other words
I want to go through the table for every day, find the total for the prior 7
days, and output the max of that total and the date it occurs.

Thanks in advance for any hints.

Ed
 
This query uses a table named CountNumber containing number 1 thru your
maximum search span of days. It can return more than one day if they have
the same total.
It prompts for a start date to begin the search.

SELECT TOP 1 CVDate([Enter start date])+[CountNUM] AS [Start Date of 7-day],
Sum(LOGBOOK.FlightTime) AS [7-day Total]
FROM LOGBOOK, CountNumber
WHERE (((LOGBOOK.FlightDate) Between CVDate([Enter start date])+[CountNUM]
And CVDate([Enter start date])+6+[CountNUM]))
GROUP BY CVDate([Enter start date])+[CountNUM]
ORDER BY Sum(LOGBOOK.FlightTime) DESC;
 
Karl,
Thanks a bunch. I'll check it out tonight.
Ed

KARL DEWEY said:
This query uses a table named CountNumber containing number 1 thru your
maximum search span of days. It can return more than one day if they have
the same total.
It prompts for a start date to begin the search.

SELECT TOP 1 CVDate([Enter start date])+[CountNUM] AS [Start Date of
7-day],
Sum(LOGBOOK.FlightTime) AS [7-day Total]
FROM LOGBOOK, CountNumber
WHERE (((LOGBOOK.FlightDate) Between CVDate([Enter start date])+[CountNUM]
And CVDate([Enter start date])+6+[CountNUM]))
GROUP BY CVDate([Enter start date])+[CountNUM]
ORDER BY Sum(LOGBOOK.FlightTime) DESC;


Ed Finley said:
I'm looking for a way to search a table that is is basicaly [FlightTime],
[FlightDate] for the highest total time in any 7 day period. In other
words
I want to go through the table for every day, find the total for the
prior 7
days, and output the max of that total and the date it occurs.

Thanks in advance for any hints.

Ed
Click to expand...
Click to expand...
 
Back
Top