The selecting query SQL code is:
'------------------------------------------------------------------------------
SELECT ETMS_ExpandedFlight.UniqueID AS UID1, ETMS_ExpandedFlight_1.UniqueID AS UID2, ETMS_ExpandedFlight.EventTime AS EventTime1, ETMS_ExpandedFlight_1.EventTime AS EventTime2, ETMS_ExpandedFlight.Latitude AS Lat1, ETMS_ExpandedFlight.Longitude AS Lon1, ETMS_ExpandedFlight_1.Latitude AS Lat2, ETMS_ExpandedFlight_1.Longitude AS Lon2, ETMS_ExpandedFlight.FlightLevel AS Level1, ETMS_ExpandedFlight_1.FlightLevel AS Level2
FROM ETMS_ExpandedFlight INNER JOIN ETMS_ExpandedFlight AS ETMS_ExpandedFlight_1 ON ETMS_ExpandedFlight.TrafficId = ETMS_ExpandedFlight_1.TrafficId
WHERE
(((ETMS_ExpandedFlight.UniqueID)=[ETMS_ExpandedFlight_1].[UniqueID]-1))
ORDER BY ETMS_ExpandedFlight.UniqueID;
'------------------------------------------------------------------------------
The executing code that effectively updates the average speed and climb/decent rate is saved into an updating query:
'------------------------------------------------------------------------------
UPDATE ETMS_ExpandedFlight INNER JOIN ETMSFlightUniqueIdPairs ON ETMS_ExpandedFlight.UniqueID = ETMSFlightUniqueIdPairs.UID1 SET ETMS_ExpandedFlight.AvgSpeed = CalculateAvgSpeed([EventTime1],[EventTime2],[Lat1],[Lon1],[Lat2],[Lon2]), ETMS_ExpandedFlight.DeltaLevel = 6000*([Level1]-[Level2])/(DateDiff('s',Format([EventTime1],'yyyy-mm-dd hh:nn:ss'),Format([EventTime2],'yyyy-mm-dd hh:nn:ss')));
'------------------------------------------------------------------------------
3.3.3.11.4. Sub step 11.4 - Save the calculated fields in the flight table To save the new calculated values in the flight table, a quick solution is used, knowing that the flight table and the expanded flight table have the same structure and similar indexes. The flight table is deleted (object deletion, not just clearing table's contents) and then the expanded flight table is renamed as flight table. The used code is:
'------------------------------------------------------------------------------
DoCmd.DeleteObject acTable, "ETMSFlight"
DoCmd.Rename "ETMSFlight", acTable, "ETMS_ExpandedFlight"
'------------------------------------------------------------------------------
Doing so, the ETMS_ExpandedFlight table will not exist any more because it was renamed, so the next sub step will restore a new empty temporary expanded flight table, just for further use.
3.3.3.11.5. Sub step 11.5 - Empty (create) the temporary expanded flight table In this sub step a new empty temporary expanded flight table will be created, because in the previous sub step it was deleted (renamed). The code is:
'------------------------------------------------------------------------------
DoCmd.DeleteObject acTable, "ETMS_ExpandedFlight"
DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "ETMS_ExpandedFlight", "ModelFlight", True
'------------------------------------------------------------------------------
3.3.3.11.6. Sub step 11.6 - Create tfTime index (TrafficId + EventTime in ETMSFlight table) Because after the calculation of the new two fields the tfTime index is deleted, it must be regenerated. The following SQL code is used:
'------------------------------------------------------------------------------
CREATE INDEX tfTime ON ETMSFlight (TrafficId, EventTime);
'------------------------------------------------------------------------------
3.3.3.11.7. Sub step 11.7 - Save dubious flights that have flight legs with average speed to high A dubious average speed is supposed to be a speed higher than 3000. To identify and save dubious flights into the "inconsistent table", the following code is used:
'------------------------------------------------------------------------------
中國航空網 www.k6050.com
航空翻譯 www.aviation.cn
本文鏈接地址：AERO2K Flight Movement Inventory Project Report(64)
