Pull from other table based on multiple criteria

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.

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.

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.