Many to many reports

[Jure]

Hi all!

I'm stuck. Really stuck. I can't wrap my head around this problem. So here's the issue:

I have 4 tables:

Users
Projects
Earnings
Hours

A member logs hours worked into a project as line items. Earnings are also logged per project as line items with date and amount.

Now I need a report that would show total earnings per user per month. I know I'm missing something but I don't know what.

Please help! Thank you!

Jure

projects-earnings.fmp12

[Fitch]

Seems like you'd first need to break down the earnings/month for each project. Then you'd need to find the total hours/month for each project. Divide one into the other to give you earnings/hour for that project for that month. You might want to store that result in a table that you can then use for lookups. Then multiply by each user's hours for that project for that month.

It's the end of the day and my brain is fried but those are my initial thoughts.

Oh, and welcome to FM Forums!

[comment]

3 hours ago, Jure said:

Now I need a report that would show total earnings per user per month. I know I'm missing something but I don't know what.

From what I see, earnings are attributed to a project, not to any specific user. And any user can log any number of hours in any project - so how exactly should the earnings be divided among the users? If it's by the number of hours logged in a specific month (as Fitch seems to think), then it gets complicated.

BTW, I don't see a date field in the Hours table, so currently you have no way to determine  the number of hours logged by a user in a specific month. 

[Jure]

Thanks guys for quick response.

Yes indeed - Number of hours is tied to project (let's say product development). Hours are only logged once - when the project is finished and product is launched.

But then earnings are perpetual, ongoing, month by month, like monthly sales from a product... Now I would like to divide this earnings among all users based on the ratio of their hours that they logged for the project (development of the product).

For example:

Tom worked 75 h on project A = 75% of total project earnings
Frank worked 25 h on project A = 25 % of project earnings

So for each month I need to basically calculate (all the earnings in that month) x (ratio of hours worked per user).  So we have many projects, many users, and many earnings... And my head explodes.

[comment]

This is not a simple problem. You're up against a blind side of the relational model: the data is there, but it's difficult to display it in the format you require. It is difficult, because no table has enough records to produce the required report.

IMHO, the simplest solution here would be to script the allocation of earnings to users as records in a new table. You'd probably want to make this a part of creating an earnings record. Once you have such table, the report becomes trivial.