Pull from other table based on multiple criteria

Jonathan_Gunnel · ‎Apr 24, 2019

Hello,

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!

Justin_Barrett · ‎Apr 25, 2019

There are several things with the design of your base that may be contributing to the difficulties you’re experiencing in trying to collect/view certain data. I realize that you’ve already got a large list of projects, but I still suggest rethinking the structure of your base to address these issues.

First, a table’s primary field is meant to hold unique data. By repeating a project’s name in multiple records in the [Projects] table, it not only creates redundancy in the data, but it creates potential confusion wherever those records are referenced in other tables.

Another issue I see is combining so much stuff into a single project line: client names, client roles, payments, etc.

I’m working up an alternate way of organizing the data that you might consider, but it’s going to take some time (and my brain is fried after an exhausting day). In short, though, I suggest rethinking the [Projects] table to contain less direct data and more links to other tables. For example, make a [Payments] table to track all payments for all projects, and link to the relevant project and client for each payment. I would try to describe more, but my brain is seriously gone, so I’ll stop trying and just leave this alone until tomorrow. However, maybe that gives you some ideas to chew on in the meantime.

Jonathan_Gunnel · ‎Apr 26, 2019

Justin,

I completely agree with everything you said, however, I have not been able to come up with a better way, which is why I made this post. I agree that ideally I would not be repeating in the primary field, but I haven’t found a way to accomplish this without losing the ability to reference an entire “project” as opposed to one aspect of a project. So yes, I’m absolutely open to restructuring the organization of the data so if/when you have suggestions, I’m happy to hear them.

Justin_Barrett · ‎Apr 29, 2019

Sorry for the delayed reply. I haven’t had any more time to put into this than I initially did when I started messing with it three days ago. However, I felt I should at least share what I’ve got so far. Most of what’s left is figuring out how to accomplish the various reporting-type fields you want (summaries of this, collections of that, etc), and you know what you need in that regard far better than I do.

The two biggest changes are as follows:

The [Projects] table contains a single line per project, and with a lot fewer fields than before (partly by design, and partly due to lack of time to fully flesh it out). Because the Author and Reviewer roles appeared to be consistent in your example, I made those into their own fields with links to the appropriate client assigned to them, rather than two fields to cover both role and assigned client.

I did something similar with payments. Instead of putting that into the [Projects] table, I made a new [Payments] table. Each payment becomes its own record, with fields to indicate the client, project, payment amount, payment year, etc.

That’s as far as I got. The main thing I was trying to do was figure out which things should (in my mind) become separate items to track (like payments), and how to eliminate redundancy (like multiple lines for projects). Feel free to copy what I’ve got and see if you can make it work for what you need. I realize it’s unfinished, but like I said, I unfortunately don’t have the time to delve any deeper. The shared sample base linked above will remain in its semi-complete state from here on out unless you’d like help with something specific, though I don’t know if my schedule will allow me much (if any) more time on this front in the near future.