I’ve created the base below with sample data. Each Project has two Clients assigned to it. Each Client receives two payments. These two payments may or may not be made within the same calendar year.
I have been able to sum up the “paid” and “outstanding” totals for each year (see last columns in Payment Year table). I have also done the same for each client (see Clients table).
What I need now is the paid and outstanding amounts for each client for each year. For example, I need to be able to see how much Bob has been paid so far this year and how much he is expected to make in the remainder of the year. I’ll also need to be able to see how much Bob made in previous years and his projected income in upcoming years. I realize I can figure this out on a case-by-case basis, but I need a single spot that can show me all of this info for each client in one space.
Note: I’m happy to have to create a ton of additional columns that I’ll later hide to get this job done, however, I strongly prefer NOT to have to create redundant rows. With the actual data, I already have 100s of “projects”, each with a minimum of two rows (one for each client). These “projects” also occur multiple times with different versions so I end up with redundant rows again each time the project happens.
Thanks for the help!