AnsweredAssumed Answered

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

Question asked by SimonLiu on May 16, 2011
Latest reply on May 17, 2011 by 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 :)

Outcomes