Parent-Join-Child but something missing today

[fishtech]

I think this should be simple but for some reason I have been banging my head today.

My database has parent records and child records. Each parent can have multiple children. Each child can have multiple parents.

The parent and child records are in the same table.

I have 3 table occurrences:

Parent table occurrence

Join table occurrence

Child table occurrence

The Join table contains 2 fields, the parent_record_id and the child_record_id.

In the Parent table occurrence I need to calculate the total of field 'Number' for all related records in the Child table occurrence.

What am I missing today?

Thanks,

b.

Edited October 6, 2009 by Guest
Added extra info.

[comment]

In the Parent table I need to calculate the total of field 'Number' for all related records in the Child table.

How about =

Sum ( Child::Number )

[fishtech]

Thanks for the answer. Sure... that's what I expected but its not working work. The calculation field is blank.

I should have included this in the first post (now edited)

"The Parent table and the Child table are actually the same table, but different table occurrences."

Thanks,

ft.

Edited October 6, 2009 by Guest

[comment]

Should make no difference. If the field returns a blank, check that (a) there are related records, ( there are values in the Number field, and © the calculation is evaluated from the correct context. Post a file if you still can't get it to work.

[efen]

see if the attached helps

You have chosen your skill level as "Expert"?

join.zip

Edited October 6, 2009 by Guest

[Vaughan]

The calculation is working for me.

BTW I recommend renaming the TOs to "parent" and "child" to make viewing things much easier. Makes the graph very unambiguous.

If you're trying to get the total of all parents you'll need to create another cal field that sums through the parent relationship.