Jump to content

Relation problem in the SUM function


This topic is 6816 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6816 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.