4 Replies Latest reply on Jun 15, 2010 12:09 PM by wigz

    Help request - summarising data in grid format



      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)

      Pilot 2


      Pilot 12


      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.

        • 1. Re: Help request - summarising data in grid format

          I suggest at least one more table. A Pilot table with Pilot, ID, Name and other fields specific to one pilot. Link it to your BTR table by pilot ID.


          The report you are imagining is called a "cross tab" report and isn't the easiest thing in the world to set up. If you can work with a report with this format:


          Pilot ID, Name, (and any other pilot fields you need)

            BTR1  Data

            BTR2 Data

            BTR3 Data


            BTR30 Data (or any nuber of BTR rows you need)


          PilotID , Name, .... (Next pilot's group of BTR data)

            BTR1  Data

            BTR2 Data

            BTR3 Data


          It's much, much simpler to set up.

          • 2. Re: Help request - summarising data in grid format

            Thanks Phil. 


            I am glad that my proposed solution is on the right tracks, however, for guys in a rush, the cross tab style report is much easier to interpret with all data being presented on 2 sheets of paper which is why the pilots like it.  Once you have generated the individual pilot summaries, Is there any easy way of printing them in side-by-side columns i.e. a parallel rather than serial summary (or am I just looking at the same, cross tab report problem?)



            • 3. Re: Help request - summarising data in grid format

              Hmmm, you might be able to get something similar by specifying printing in columns, accross first (Printing tab in layout setup). If you can't get that to work the way you want, you're back to the same crosstab issue. Don't misunderstand here, you can get that type of layout set up. It's just more work and less flexible so you don't want to go that way unless you have to.


              Specifically, there's a horizontal portal trick you can add to a pilots table that displays your data in columns. You set up a relationship between Pilots and your BTR records that links them by Pilot ID (and possibly some filtering fields that will omit past records that are no longer relevant.) For column 1, you add the portal and set it to display row 1 of 1. For the next column, you add a copy of the same portal but set it to display row 2 and so forth for each column...

              • 4. Re: Help request - summarising data in grid format

                Thanks Phil,


                I have played with this and managed to get pretty much what I need by creating a column report and conditionally formatting the data boxes to identify out of date currencies.  The only problem I now have is that I cannot get all data easily on one page - there are only 12 pilots so previously we could have 2 tables per landscape printed page, one above the other.  It is a bit long-winded but I have gotten round this by creating 2 virtually identical layouts with BTRs 1 - 15 on the 1st one and 16 - 30 on the other - I get 2 pages vice 1 but at least I have solved the grid layout problem that I started with.


                Thanks again,