4 Replies Latest reply on May 29, 2011 9:10 AM by SimonLiu

    Problem to get statistics across two related tables by using a third table

    SimonLiu

      Title

      Problem to get statistics across two related tables by using a third table

      Post

      I have two related tables:

      --------------------------------------------------------------------------------------
           Sheet (Parent)
           Invoice (Child)
      --------------------------------------------------------------------------------------
      Both of them have multiple fields. In "Sheet" I created:
      --------------------------------------------------------------------------------------
           Sheet_ID   --------(serial)
           Project_Name   ---------(text)
           Submission_Status   --------(number: 0,1)
           Payment_Status   ---------(number: 0,1)
      --------------------------------------------------------------------------------------
      In "Invoice" I created:
      --------------------------------------------------------------------------------------
           Sheet_ID   ------------(serial)
           Purpose_Category   -----------(text: A, B)
           Purpose_Detail   ------------(text: X, Y)
           Amount   -----------(number)
      --------------------------------------------------------------------------------------
      They are linked by key field "Sheet_ID", and each has many records.
      For statistic purpose, I want to create a new table to calculate the total amount by Invoice's [Project_Name], [Submission_Status], [Payment_Status], [Purpose_Category], and [Purpose_Detail].
      So I created a table named "Statistics", who has corresponding fields, and has a summary field summarizing Invoice::Amount. Then I related "Statistics" with "Sheet":
      --------------------------------------------------------------------------------------
           Sheet::Project_Name = Statistics::Project
           Sheet::Submission_Status = Statistics::Submission
           Sheet::Payment_Status = Statistics::Payment
      --------------------------------------------------------------------------------------
      I thought the next step would be relate "Statistics" with "Invoice", so I did:
      --------------------------------------------------------------------------------------
           Invoice::Purpose_Category = Statistics::Category
           Invoice::Purpose_Detail = Statistics::Detail
      --------------------------------------------------------------------------------------
      This action inevitably created a new table occurrence named "Statistics 2" and here comes the problem: 
      it seemed that it doesn't work this way.
      I know this was trying to relate a table to both the parent table and the child table.
      I really don't want to duplicate those Project and Submission fields in the Invoice Table and make them calculated text fields via relationship Sheet-Invoice.
      May anyone spare a few minutes to tell me what to do?
      Many thanks in advance :)

        • 1. Re: Problem to get statistics across two related tables by using a third table
          philmodjunk

          First detail (may be a mistake in how you wrote this  up rather than a mistake in your Database Design):

          Invoice::Sheet_ID should not be defined as a serial number. It should be a field of type number (best choice) or text whichever matches the data type of Sheet::Sheet_ID.

          and [statistices] has a summary field summarizing Invoice::Amount.

          I don't see how you did that as Summary fields can only summarize a field in the same table. Perhaps you added such a summary field to Invoices? or you defined a caculation field that uses Sum to calculate the total?

          To related statistics to Invoices, Select Invoice and click the button with two green plus signs. This will produce a new table occurrence of Invoice that you can then link to Statistics in a relationship.

          If "Table Occurrence" is a new term, you may want to read this tutorial on the subject: 

          Tutorial: What are Table Occurrences?

          • 2. Re: Problem to get statistics across two related tables by using a third table
            SimonLiu

            Thank you very much for your swift reply.

            About the serial thing. I wrote it wrong :) The Invoice::Sheet_ID is not serial but a number field linked to Sheet::Sheet_ID.

            For summary I defined a calculation field using SUM to calculate the total in Invoice.

            For the "table occurrence" part, as I have created a relationship Sheet-Statistic, when I simply add new relationship Invoice-Statistics, it shows this:

            There cannot be more than one relational path between any two tables in the graph. Another occurrence of one of the tables must be added to the graph.

            When I click OK, it creates "Statistics 2" for me.

            -----------------------------------------------------------------------------------------------

            So maybe I should reorganize my question like this:

            As table Invoice has related to Sheet, is it possible to get a total of all Invoices related to a specific sheet by using a third statistic table?

            Let's say I want to sum up invoices meeting these criteria:

            Sheet::Project_Name = "Project A"

            Sheet::Submission_Status = 1

            Sheet::Payment_Status = 1

            Invoice::Purpose_Category = A

            Invoice::Purpose_Detail = X

            Those criteria involves those relationships: Sheet-Invoice, Sheet-Statistics, Invoice-Statistics.

            This seems (forgive me if I am using the wrong word) created a loop.

            Anyway to solve it?

            • 3. Re: Problem to get statistics across two related tables by using a third table
              philmodjunk

              "For the "table occurrence" part, as I have created a relationship Sheet-Statistic, when I simply add new relationship Invoice-Statistics, it shows this:

              There cannot be more than one relational path between any two tables in the graph. Another occurrence of one of the tables must be added to the graph.

              When I click OK, it creates "Statistics 2" for me."

              This is exactly what I would expect to happen. Statistics 2 is a new table occurrence of statistics. Did you read the tutorial to which I provided a link?

              • 4. Re: Problem to get statistics across two related tables by using a third table
                SimonLiu

                Thanks.

                I did read carefully and now I know why this would happen.

                I just wonder whether the "same table, 3 relationship with table A and 2 relationship with table B"(as written in the lower part) would be achieved. If so, what is the approach.

                -------------------------------------------------------------------------

                I came up with a new way to do so:

                1) add three more fields:

                > Invoice::Project---------(Auto-enter Calculation = Sheet::Project_Name)

                > Invoice::Submission---------(Auto-enter Calculation = Sheet::Submission_Status)

                > Invoice::Payment---------(Auto-enter Calculation = Sheet::Payment_Status)

                2) use five relationship altogether:

                 > Invoice::Project = Statistics::Project

                > Invoice::Submission = Statistics::Submission

                > Invoice::Payment = Statistics::Payment

                > Invoice::Purpose_Category = Statistics::Category

                > Invoice::Purpose_Detail = Statistics::Detail

                3) populate the records.

                4) do the Calculation SUM

                -------------------------------------------------------------------------

                This time it works. However the table(s) doesn't seem so neat.

                Any better idea? Thanks!