Help request - summarising data in grid format
I am a keen self-taught amateur database developer but can't always find the easy way to solve problems - PhilModJunk described my last dilemma as a 'Gordian Knot', hopefully this one won't end up the same way!! I have tried a Forum search but haven't found any threads that point me in the right direction.
I am trying to develop a database to track the flying hours and currency requirements for 12 pilots. Each pilot needs to track approximately 30 Basic Training Requirements (aka BTRs) and each BTR is either valid for a set time (eg a pilot's instrument rating test is valid for 13 months) or the pilot needs to conduct a minimum number of a specific BTR in a rolling 6-month period. At present the data is presented in an Excel spreadsheet and when a pilot conducts a BTR the existing date value is overwritten or running total updated manually and conditional formatting used to assess daily currencies and forecast upcoming training requirements.
I have developed a scripted layout which captures details from every sortie that each pilot flies and then creates an individual record for each BTR; the fields are:
RecordID (Primary key - autocreate serial number)
BTR Date (date that event was practiced)
BTR Code (unique identifier for the type of BTR flown - eg IRT for instrument rating test, SIM for flight simulator sortie)
BTR Pilot (name of pilot who the record relates to)
Each sortie record can generate any number of related BTR records.
What I am struggling with is how to collate and present the data in a useable format ideally a grid with one row for each pilot and columns containing maximums, sums, counts etc depending on the BTR currency requirements:
BTR1 BTR2 BTR3 ...................... BTR30
Pilot 1 Max(date) Count(last 6 months) Sum(last 6 months) Max(Date)
The aim would be to conditionally format the summarised data to highlight currencies that are expired or about to expire.
Based on PMJ's advice on my last post; rather than create table with 360+ fields (one for each cell on the grid - 30 BTR x 12 pilots) I have struggled to find a cleaner and more scaleable solution (BTR or pilot numbers may change). At present my envisaged solution is to add 30 BTR fields to 'Pilots' table, script 'Finds' to generate the required summary data and then copy the result into the corresponding Pilots::BTR(n) field. I assume that a portal layout could be used to create the sort of grid I am looking for.
The problem that I can see, if I go ahead with my current solution, is that if the BTR requirements change I will have to do a lot of tweaking (table fields and probably scripts) - is there a better way?
Thanks for any help/guidance that you can offer.