### Title

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

### Post

I have two related tables:

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

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 :)

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?

*"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?*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!

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?