3 Replies Latest reply on Dec 2, 2010 2:56 PM by philmodjunk

    Creating a report that shows Averages for 3 different tables.

    cmac

      Title

      Creating a report that shows Averages for 3 different tables.

      Post

      I'm trying to create a report that shows the averages of 3 different tables on one report.  I have 3 tables:

      Initial Screening

      Second Screening

      Third Screening

      They are all related by the clientID field

      For each one of these tables, I created a summary field that shows the average weight.  The problem is when I create a report, Only one average result (initial screening average weight) is displaying on the report.  When I add the other two average fields to the report, the report isn't showing the second and third screening average weights. 

      Any suggestions???

        • 1. Re: Creating a report that shows Averages for 3 different tables.
          philmodjunk

          There may be details to your system not stated in your post that would make this a bad idea, but it looks to me that you could put all your screening records in the same table and use a field to label them as "initial", "second" or "third". This would enable you to create a summary report where you group the records by screening type and place what is now a single summary field in a subsummary part when sorted by screening type to show the average weights for all three sets of data.

          If you decide to keep your separate tables, you'll need two new relationships.

          Initial Screening::ClientID X AllSecondScreening::ClientID
          Initial Screening::ClientID X AllThirdScreening::ClientID

          AllSecondScreening is a second table occurrence of Second Screening and AllThirdScreening is a second table occurrence of Third Screening.

          Now you can add your summary fields from AllsecondScreening and AllThirdScreening to get your averages of every record in these two related tables.

          If "Table Occurrence" is an unfamiliar term, see this link: Tutorial: What are Table Occurrences?

          • 2. Re: Creating a report that shows Averages for 3 different tables.
            cmac

            Thank you... I created two seperate table occurences for the second and third screening tables and linked them to the original initial screening table based on the client ID.  Each table has a date screened field and a company ID field.  Now, my next obstacle.....In creating my report, I created an input window for the user to enter the company id and the date screened.  The date screened is coming from the initial screening table.  So for example, the user enters:

            Date screened = 07/*/2010 (Month of July)

            Company ID = Workers House

            I want to see the average weight on the report for each table (initial, second and third.) In my script, do I need to sort each one of the occurences based on the company ID??  How do I get the correct average weight in the report that is in each table based on the criterias entered? 

            • 3. Re: Creating a report that shows Averages for 3 different tables.
              philmodjunk

              This is where combining your data in one table would make this report much easier to set up. You originally specified that you wanted the "3 different tables on one report". Now, you are specifying that these averages be of a specified subset of the records of each table. This will be harder to set up if you insist on keeping the data in separate tables.

              It seems like you should have at least these tables here:

              Clients, All data about the client, including client ID, company ID, and initial screening date

              Companies, all data about each company

              Screenings, where you would record the screening data for all three screenings.

              This structure would make it fairly straightforward to set up a report based on screenings that will give you all three averages. You'd generate this report for the desired group of clients, companies and/or screening dates by performing a find on this layout, specifiying data in the related tables as well as screenings.

              These tables should also be linked by serial number ID fields, not names. Names of companies and individuals are not unique and change from time to time and this will create problems for your data when you either get two different entities with the same name or a person or company changes their name.