calculation from relationship involving multiple tables

[aeu]

I'm stuck on a problem that seems like there should be an easy answer, but I can't seem to get the answer on my own (and didn't find anything searching the forum).

The relevant tables and keys look like this:

-----

Project Table:

ProjectID

ContactID

TotalPaidToContactForProject

-----

Payment Table:

ContactID

-----

PaymentLine Table:

ProjectID

AmountPaid

I simply want a calculation field in the Project table that calculates the total amount paid to a contact on a particular project (we'll call it TotalPaidToContactForProject) that aggregates/sums the AmountPaid field from PaymentLine based on a combination key relationship of both ContactID (in the Payment table) and ProjectID (in the PaymentLine table). Keep in mind that the ContactID is set in the Payment table, but the payment might include multiple projects, so the project ID is set in the PaymentLine table. Hence my conundrum.

Assume for this example that other elements such as the Contact table and certain connecting key fields exist as they should (i.e. PaymentID field linking Payment and PaymentLine tables).

My failed solution was to try creating a ContactID calc field in PaymentLine to pull over the ContactID from the Payment table, but since it can't be indexed it will not work for summing from the Project table.

Any help would be appreciated!

[comment]

a ContactID calc field in PaymentLine to pull over the ContactID from the Payment table

Indexing aside for a moment: how would you do this - when Payment and PaymentLine have no common fields?

The way you describe it, PaymentLine records are related to a specific project, and a project is related to ONE contact only - therefore ALL PaymentLine amounts have been paid to the owner of the parent project.

Edited May 7, 2010 by Guest

[aeu]

I left out certain given fields and data to keep things simple and focus on the tables and fields in question. Go ahead and assume there is a Contact table and also that the Payment and PaymentLine tables are related by a PaymentID key field. Sorry for any confusion.

[comment]

OK, so if I am not utterly confused yet, you have something like:

Contacts -< Projects -< PaymentLines >- Payments >- [Contacts]

If so, TotalPaidToContactForProject (in Projects) should be =

Sum ( PaymentLines::Amount )

There is no need for a ContactID in PaymentLines, since there is a ProjectID - and the project can belong to only one contact. IOW, the total amount paid to a contact on a particular project is the total amount paid on a particular project - unless I am still missing something...

[aeu]

Yes, you are correct. I was over-complicating it because of an ERD oversight. Thank you for your help!