AERO2K Flight Movement Inventory Project Report(50)

曝光台 注意防骗 网曝天猫店富美金盛家居专营店坑蒙拐骗欺诈消费者

3.2.3.1.6.  Sub step 4.2 - Changing imported flight data to AERO2K specific data type
The second sub step calls an appending query that converts the imported data in the first sub step to the correct AERO2K data type. The appending query SQL code is:
'------------------------------------------------------------------------------
INSERT INTO AmocFlight ( CallSign, EventTime, Latitude, Longitude, FlightLevel, GroundSpeed, Source )
SELECT [CallSign], IIf(Left([MessageTime],2)<24,DateAdd('d',-1,DateValue([Forms]![ImportAmoc].FlightFileDate)) & ' ' & Left([MessageTime],2) & ':' & Mid([MessageTime],4,2) & ':' & Right([MessageTime],2),IIf(Left([MessageTime],2)<48,DateValue([Forms]![Import Amoc].FlightFileDate) & ' ' & (Left([MessageTime],2)-24) & ':' & Mid([MessageTime],4,2) & ':' & Right([MessageTime],2),DateAdd('d',1,DateValue([Forms]![ImportAmoc].FlightFil eDate)) & ' ' & (Left([MessageTime],2)-48) & ':' & Mid([MessageTime],4,2) & ':' & Right([MessageTime],2))) AS EventTime, [Latitude], [Longitude], [FlightLevel], [GroundSpeed], 'AMOC' AS Source
FROM Amoc_ImportedFlight;
'------------------------------------------------------------------------------

3.2.3.1.7. Sub step 4.3 - Delete temporary table Amoc_ImportedFlight
The third sub step empties the temporary table Amoc_ImportedFlight. The executed code is:
'------------------------------------------------------------------------------
DoCmd.DeleteObject acTable, "Amoc_ImportedFlight"
DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "Amoc_ImportedFlight", "ModelAmoc_ImportedFlight", True
'------------------------------------------------------------------------------
The state of the flight table after this step is not final. Some modification will take place after executing the following steps, like modifying the TrafficId for each leg to link the legs with their corresponding heads, updating the flight legs that belong to the same flight but have the same event time, converting the date/time fields' values to GMT time, inserting new legs for the flights that pass the midnight or even calculating the average speed and climb/descent rate for each two consecutive flight legs that belong to the same flight.

 

Step 5 - Link the two imported tables

This step is the most important step of the importing procedure. It links the flight heads with the flight legs. Table 54 shows how AmocTraffic and AmocFlight tables are linked:
Table 54: Linked AmocTraffic and AmocFlight tables.

'------------------------------------------------------------------------------
CREATE INDEX csTime ON AmocFlight (CallSign, EventTime);
'------------------------------------------------------------------------------

3.2.3.1.9. Sub step 5.2 - Link each flight head with its flight legs
The principle of this sub step is to link a flight leg to a flight head if the flight leg's CallSign is identical with the head's CallSign and the flight leg's EventTime is between head's DepartureTime and MaxStopTime (possible arrival time). If the condition is accomplished the flight leg's TrafficId will be updated to flight head's TrafficId. Otherwise the TrafficId will remain 0. The update query SQL code is simple:
'------------------------------------------------------------------------------ 
UPDATE  AmocTraffic  INNER  JOIN  AmocFlight  ON 
[AmocTraffic].[CallSign]=[AmocFlight].[CallSign]  SET  AmocFlight.TrafficId  = 
[AmocTraffic].[TrafficId] 
WHERE  ((([AmocFlight].[EventTime])  Between  [DepartureTime]  And 
[MaxStopTime])); '------------------------------------------------------------------------------ 

3.2.3.1.10. Sub step 5.3 - Delete the temporary index csTime (CallSign + EventTime)
　
中国航空网 www.aero.cn
航空翻译 www.aviation.cn
本文链接地址：AERO2K Flight Movement Inventory Project Report(50)