Relation problem in the SUM function

[Ersen Kavak]

Hi all,

I have 10 tables in my db...

My problem is in the 3 of these tables...to talk more explicitly the names are

projects, orderforms and subbudgets

I have a relationship between projects and orderforms on the field projectacronym

I have a relationship between subbudgets and projects on the field subbudgetID

Thus, orderforms are indirectly related to the subbudget fields... And this indirect relationship is the one I really need and it is the source of the problem...

For instance, when I use this while defining a value list with the include only related record option, it works as expected...

However, when I use it in the sum function, by trying to take the SUM of related orderforms under a subbudget, it takes the grant sum(which means the sum of the orderforms which are related to the whole project)... And unfortunately, I have multiple subbudgets under a project. And multiple orderforms under each subbudget...

ps: I tried creating a different relationship between orderforms and subbudgets, however this creates problems in my db design and hardens lots of things, thus if anybody has an idea why this SUM is not working in this kind of a relationship, waiting for any ideas...

Best

Ersen

[SlimJim]

Thus, orderforms are indirectly related to the subbudget

So if you start with a subbudget then it is related to a project and that is related to a bunch of orderforms. So doesn't this relationship relate the subbudget to all the orderforms related to the project? which would explain why the SUM produces the sum of all the orderforms related to the project.

To produce a SUM of orderforms related to a subbudget then you need a relationship between projects and orderforms which takes the subbudget ID into account; I assume there is a subbudget ID in the orderform table. So you need a second TO of Orderforms which relates to projects both by projectacronym and SubbudgetID and then calculate the SUM via that TO.