Sum different records of same date, same table

[The Gardener]

Hello,

I have attached the file I am working, which I am developing to record data on mutual funds held within multiple portfolios [groups of mutual funds]

One table, securities monthly data main and the associated table occurrences has the data on the funds and various Sum calculations.

Another table, portfolios aggregate values main is related to the occurrences of the table securities monthly data main to produce several calculations.

It is in this table and the one table occurrence self related to it, that my problem is.

I have entered only minimal records.

The two records that illustrate my problem are the 'td waterhouse rsp' and 'rbc rsp' [t portfolio name field] records. These are two different portfolios.

They both have the same record date.

They both have a number amount in the c aggregate portfolio book value field, this is a calculation field that produces the total book value of all the funds held in a specific portfolio on a specific date using related data from the securities monthly data main table.

What I want to be able to do in a calculation field is to calculate the sum of the c aggregate book value field record amounts, on a particular date, for both different portfolios - a total sum of both portfolios.

Then I should be able to use the same method to produce other similar total sum data.

And, hopefully, then calculate, percentages of totals [for example a portfolio is a percent of all portfolios]

If you go to the fields in the table portfolios aggregate values main and go down to the bottom three you will see calculations, which do not work.

c aggregate all portfolios book value Copy, only sums one portfolio not the two.

Do I have a relationship problem?

I know the end but not how to get there, so help will be appreciated.

Thanks in advance.

security portfolio.fp7.zip

[comment]

Your file is too complicated (and rather messy, if I may say so) to understand in a reasonable amount of time. Can you explain, in simple terms, what are you trying to accomplish here?

Usually, to "sum different records of the same [anything]" you would use a report with sub-summary parts.

[The Gardener]

Hello,

Thank you for replying. I have read my post over and indeed is is not very clear.

So, I will try to write a clearer explanation for you

I have a table, portfolios aggregate main with these fields:

t portfolio name - text, indexed

d portfolio value date - date, indexed

c aggregate portfolio book value - unstored, this is Sum calculation through a relationship to another table which holds records of the book values of mutual funds held in a particular portfolio, and sums those book values records held in a particular portfolio , on a particular date

c month - indexed, from portfolios aggregate main Month, = ( d portfolio value date )

d year - indexed, from portfolios aggregate main Year, = ( d portfolio value date )

c aggregate all portfolios book value - unstored, from portfolios aggregate value all portfolios agg, = Case(

c month = portfolios aggregate value all portfolios agg::c month

and

c year = portfolios aggregate value all portfolios agg::c year;

Sum ( portfolios aggregate value all portfolios agg::c aggregate portfolio book value);0)

I also have made a selfjoin table occurrence

portfolios values all portfolios agg

with the relationship

c month=c month and c year=c year

the field, c aggregate all portfolios book value, uses this relationship.

So, I have the following records

t portfolio name - 'td rsp'

d portfolio value date - '31/03/2007'

c aggregate portfolio book value - '100'

c aggregate all portfolios book value

a second record

t portfolio name - 'rbc rsp'

d portfolio value date - '31/03/2007'

c aggregate portfolio book value - '100'

c aggregate all portfolios book value

My problem is that at present,

c aggregate all portfolios book value, produces the result - '100'

in each record above

What I want to have is a sum in the field,c aggregate all portfolios book value

of these two records c aggregate portfolio book value fields

when the d portfolio value date is the same in both records

ie. a sum of '200'

I hope this is a clearer explanation and will serve to hopefully prompt and answer to my question

Thanks in advance

[comment]

I think you should have a table of Portfolios, a table of Securities and a join table of Holdings (where each record represents the quantity of one security in one portfolio):

Portfolios -< Holdings >- Securities

Then, supposing you also have a table of Prices (where each record represents the price of a security on a specific date):

Portfolios -< Holdings >- Securities -< Prices

you can add a global gDate field to the Securities table and construct a new relationship to the Prices table as:

Securities::SecurityID -< Prices 2::SecurityID

AND

Securities::gDate -< Prices 2::Date

This will allow you to calculate the value of each holding in the Holdings table as =

Quantity * Prices 2::Price

and sum up the results in the Portfolios table to get the value of each portfolio on the selected date.

[The Gardener]

Hello,

Thank you very much for replying. Just home from a long day, so will have to look at your post later in relation to my current file.

I can see immediately that I should make some changes to my current file which will improve it.

I will have to see if I can, with these recommendations, adapt and/or extend it to achieve my objective of being able to sum the value of all different portfolios on a particular date.

Thanks again.

Mark