I'm trying to answer a few fundamental/basic questions before I move any further with my application. If anyone has any recommendations, or suggestions where I could go for some insight I would really appreciate it.
I have many fields of information that I want to be able to data mine and report on. My ultimate problem is that I don't know if I need to create individual queries for every calculation/comparison... OR, if I can store the 4 or 5 equations I use somewhere, and the user just selects the indicators, the calculation type, and the time frame; hit the button and presto.
If there are 10 fields of indicators and 10 fields of data I want to compare the indicators to, and for each of those I want 1 month, 3 month, 12 month rates, control limits, etc. do I need to build 400 queries (10 indicators, 10 data points, 4 calculations)?? OR... Is there a way to pass the selected indicator fields through a table or query where the formulas reside and then send that to the graph?
Example:
tblMaintenanceHistory - MxDate, Source (pilot, mechanic, engineering, etc.), SystemCode (23 for Communications, 24 for Electrical, 27 for Flight Controls, and 20 more), TailNumber (250+ different aircraft identifiers), FleetType (9 different fleet types), FlightNumber, EventGateway (150 different gateways), Discrepancy, CorrectiveAction, and many more.
tblExceptionHistory (events of departure delays) - ExDate, Type (Cancellation, Delay, Informational), Delay Time (time flight was delayed), DelayCode (20 different codes that identify the department or process responsible), TailNumber, FleetType, FlightNumber, EventGateway, Reason, Action Taken, and many more.
tblFlightHistory (all aircraft flights) - FltDate, TailNumber, DepartingGateway, DestinationGateway, FlightNumber, TimeDeparted, TimeLanded, and many many more.
Using only the 3 tables above and there's many more, I may want to see:
3 month rate of selected Source generated discrepancies per 1000 departures from selected Gateway for selected SystemCode for each month over specific time, compared to 12 month rate of same. May want to see this information for one specific TailNumber or for one specific FleetType. May want to compare this to the Exception rate based on same criteria.
Or, 3 month rate of selected DelayCode Exceptions that were delayed at least 60 minutes, grouped by the SystemCode or grouped by the Gateway, for a specific FleetType for selected date range.
As you can imagine, the combinations are limitless. I realize I can reduce the number of queries with filters or parameters, but there are still many many combinations where I'm comparing the rate of one indicator to another. Although there's 50 or more fields I want to cut and slice, there's basically only 4 or 5 calculations I perform - 1 month rates, 3 month rates, 12 month rates, and performance standards based on standard deviations are the most used.
Thanks in advance for any assistance!!
Mike
I have many fields of information that I want to be able to data mine and report on. My ultimate problem is that I don't know if I need to create individual queries for every calculation/comparison... OR, if I can store the 4 or 5 equations I use somewhere, and the user just selects the indicators, the calculation type, and the time frame; hit the button and presto.
If there are 10 fields of indicators and 10 fields of data I want to compare the indicators to, and for each of those I want 1 month, 3 month, 12 month rates, control limits, etc. do I need to build 400 queries (10 indicators, 10 data points, 4 calculations)?? OR... Is there a way to pass the selected indicator fields through a table or query where the formulas reside and then send that to the graph?
Example:
tblMaintenanceHistory - MxDate, Source (pilot, mechanic, engineering, etc.), SystemCode (23 for Communications, 24 for Electrical, 27 for Flight Controls, and 20 more), TailNumber (250+ different aircraft identifiers), FleetType (9 different fleet types), FlightNumber, EventGateway (150 different gateways), Discrepancy, CorrectiveAction, and many more.
tblExceptionHistory (events of departure delays) - ExDate, Type (Cancellation, Delay, Informational), Delay Time (time flight was delayed), DelayCode (20 different codes that identify the department or process responsible), TailNumber, FleetType, FlightNumber, EventGateway, Reason, Action Taken, and many more.
tblFlightHistory (all aircraft flights) - FltDate, TailNumber, DepartingGateway, DestinationGateway, FlightNumber, TimeDeparted, TimeLanded, and many many more.
Using only the 3 tables above and there's many more, I may want to see:
3 month rate of selected Source generated discrepancies per 1000 departures from selected Gateway for selected SystemCode for each month over specific time, compared to 12 month rate of same. May want to see this information for one specific TailNumber or for one specific FleetType. May want to compare this to the Exception rate based on same criteria.
Or, 3 month rate of selected DelayCode Exceptions that were delayed at least 60 minutes, grouped by the SystemCode or grouped by the Gateway, for a specific FleetType for selected date range.
As you can imagine, the combinations are limitless. I realize I can reduce the number of queries with filters or parameters, but there are still many many combinations where I'm comparing the rate of one indicator to another. Although there's 50 or more fields I want to cut and slice, there's basically only 4 or 5 calculations I perform - 1 month rates, 3 month rates, 12 month rates, and performance standards based on standard deviations are the most used.
Thanks in advance for any assistance!!
Mike
