SUM function in relationship

[mikeytt]

I am stuck on this...

The data i have consists of 3 fields.

Code is an identifier

Views is number of times a "code" has been seen

Type can be either A or B

Code    Views    Type    

001       5        A   

001       5        A    

001       3        A   

001       7        B

002       3        A

002       1        A

002       2        B

002       5        B

I can work out the total "views" for each "code" using the SUM function across a self join (code to code)

e.g in the example above, "001" has a total of 20 views (5+5+3+7), "002" has a total of 11 views (3+1+2+5).

What i want to do is find the totals for "code" and "type", so in the example above, "001" has a total of 13 views (5+5+3) for "type" A and 7 views for type "B". "002" has a total of 4 views (3+1) for "type" A and 7 views (5+2) for "type" B

I just can't work out how to get those figures calculated. Have attached an example file with the above data.

Any help greatly appreciated

TEST.fp7.zip

[comment]

Well, you could add a calculation field =

Case ( Type = "A" ; Views )

then sum this field separately. The other sum is of course the difference between this and the total.

However, the "correct" Filemaker way to do this is to produce a report, sub-summarized by Code and Type. Then all you need is one summary field = Total of Views.