Sum Total from previous records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to create a total from 2 fields, [Date] and [FlightTime].
The 2 day total is the sum of the [FlightTime] for [Date] and [FlightTime]
for [Date]-1 (previous day).
 
You want to group by the pilot (or aircraft?) and the date, and sum the
flying hours for that date and the previous date?

The query will look something like this:

SELECT tblFlight.PilotID,
tblFlight.FlightDate,
(SELECT Sum(Dupe.FlightTime) AS SumOfFlightTime
FROM tblFlight AS Dupe
WHERE (Dupe.PilotID = tblFlight.PilotID)
AND (Dupe.FlightDate Between tblFlight.FlightDate -1 And
tblFlight.FlightDate))
AS TwoDayFlightTime
FROM tblFlight
GROUP BY tblFlight.PilotID, tblFlight.FlightDate;


The 3rd field is a subquery, so the results will be read-only. If subqueries
are new, here is Microsoft's introduction:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Note that "Date" is not a good choice of field name: it will probably work
okay here, but in some contexts Access will misunderstand it as the system
date. Hence "FlightDate" in the example above.
 
Yes, group by pilot. That query worked perfectly thank you very much. I also
want to expand it to other fields for 3,7,30 day totals as well. what is the
easiest way of accomplishing that ??
Regards
Warren

Allen Browne said:
You want to group by the pilot (or aircraft?) and the date, and sum the
flying hours for that date and the previous date?

The query will look something like this:

SELECT tblFlight.PilotID,
tblFlight.FlightDate,
(SELECT Sum(Dupe.FlightTime) AS SumOfFlightTime
FROM tblFlight AS Dupe
WHERE (Dupe.PilotID = tblFlight.PilotID)
AND (Dupe.FlightDate Between tblFlight.FlightDate -1 And
tblFlight.FlightDate))
AS TwoDayFlightTime
FROM tblFlight
GROUP BY tblFlight.PilotID, tblFlight.FlightDate;


The 3rd field is a subquery, so the results will be read-only. If subqueries
are new, here is Microsoft's introduction:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Note that "Date" is not a good choice of field name: it will probably work
okay here, but in some contexts Access will misunderstand it as the system
date. Hence "FlightDate" in the example above.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wazza580 said:
I need to be able to create a total from 2 fields, [Date] and [FlightTime].
The 2 day total is the sum of the [FlightTime] for [Date] and [FlightTime]
for [Date]-1 (previous day).
Click to expand...
Click to expand...
 
Just add 3 more subqueries to the SELECT clause.
They will be similiar, but with the different date range:

SELECT tblFlight.PilotID,

tblFlight.FlightDate,

(SELECT Sum(Dupe.FlightTime) AS SumOfFlightTime
FROM tblFlight AS Dupe
WHERE (Dupe.PilotID = tblFlight.PilotID)
AND (Dupe.FlightDate Between tblFlight.FlightDate -1 And
tblFlight.FlightDate)) AS TwoDayFlightTime,

(SELECT Sum(Dupe3.FlightTime) AS SumOfFlightTime
FROM tblFlight AS Dupe3
WHERE (Dupe3.PilotID = tblFlight.PilotID)
AND (Dupe3.FlightDate Between tblFlight.FlightDate - 2 And
tblFlight.FlightDate)) AS TwoDayFlightTime AS ThreeDayFlightTime,

(SELECT ...
) AS SevenDayFlightTime,

(SELECT ...
) AS ThirtyDayFlightTime

FROM tblFlight
GROUP BY tblFlight.PilotID, tblFlight.FlightDate;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wazza580 said:
Yes, group by pilot. That query worked perfectly thank you very much. I
also
want to expand it to other fields for 3,7,30 day totals as well. what is
the
easiest way of accomplishing that ??
Regards
Warren

Allen Browne said:
You want to group by the pilot (or aircraft?) and the date, and sum the
flying hours for that date and the previous date?

The query will look something like this:

SELECT tblFlight.PilotID,
tblFlight.FlightDate,
(SELECT Sum(Dupe.FlightTime) AS SumOfFlightTime
FROM tblFlight AS Dupe
WHERE (Dupe.PilotID = tblFlight.PilotID)
AND (Dupe.FlightDate Between tblFlight.FlightDate -1 And
tblFlight.FlightDate))
AS TwoDayFlightTime
FROM tblFlight
GROUP BY tblFlight.PilotID, tblFlight.FlightDate;


The 3rd field is a subquery, so the results will be read-only. If
subqueries
are new, here is Microsoft's introduction:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Note that "Date" is not a good choice of field name: it will probably
work
okay here, but in some contexts Access will misunderstand it as the
system
date. Hence "FlightDate" in the example above.

Wazza580 said:
I need to be able to create a total from 2 fields, [Date] and
[FlightTime].
The 2 day total is the sum of the [FlightTime] for [Date] and
[FlightTime]
for [Date]-1 (previous day).
Click to expand...
Click to expand...
Click to expand...
 
Back
Top