summary based on year

[Karma]

Hi,

I have a database for members. Each year they pay a particular amount depending on their status.

In the database i have a table for payments, in that table are two fields:

Year

Amount Paid

For each member a new 'payment' record is created.

On a related table i have two more fields:

Year (a drop down list containing years based on the payments table)

Total Revenue

i want to be able to select a year and have the revenue appear in the revenue field.

I know the basic consept of the solution:

'the sum of {amount paid::payments} when {year::payments} = {year::main}

just not how to implement it, or the functions to use.

Apologies if i have not explained this too clearly. I would put up an example if needed, but the database is way too big to put up...

Thanks in advance,

Nat

[-Queue-]

Create a relationship from Main::gYear (formatted as a list) to Payments::Year, and a calculation field in Main of Sum(Payments::AmountPaid).

[Karma]

Already did that, and the sum field calculates to total for all years, not just the year selected...

[-Queue-]

Is the relationship using the = operator? It sounds as if it may be using the X operator, which would relate each Main record to all Payments records. Also, make sure the calculation field is summing the correct relationship (if you have more than one relationship to Payments).

[Karma]

I looked at what i had, and i had the summary on the wrong Payments table... It works now, thanks!