Ok now that i'm in front of the database and have some of your answers let me try to add some visual aids and more explanations.
I've attached a screenshot of a form called "sponsorships". That is my end goal (obviously with the data populating tables in a way that's usable later. On the main section of the form there are fields for Brand Id (Combobox reference to the sponsor company), Dept ID (should be CompanyId, reference to Sponsored Company), Sponsorship type (Reference to SPType table), Cost (USD amount for total sponsorship), and 3 notes fields for general info. All of that is easy and a direct pull from the sponsorships table.
Where i run into issues is the subform (or whatever i need to replace it with). Below the above mentioned fields i need a list of departments (labeled as "internal teams") for that sponsorship. Those departments will need to be only departments that are part of the Sponsored company selected above, there could be a list from 0 to many of them for each sponsorship. So for example for Sponsorship 1 between Sponsor A and company 2 there could be 3 of company 2's departments involved, in sponsorship 2 between Sponsor A and Company 2 there could be 8 of company 2's departments involved, and Sponsorship 3 between Sponsor A and Company 2 there could be generically with company 2 with no departments involved (so 0).
What i need it to do is to be able to fill out what sponsor and company the partnership is between, then let me add multiple rows of departments of the selected company. then somehow save that in tables somehow. When i cycle between records on the form, I'd like it to cycle between sponsorships showing a list of the depts already logged. I will also be adding brands (which are a subset of the sponsors same as how departments are part of companies), but i assume i can just replicate the solution for departments for that (however you might see some references to it in the relationships picture)
I've also added a screenshot of my relationships table, which i recognize is a mess. I'm just trying to get this functioning and then will be doing some renaming etc to clean it up. This will be used as a log of data, not as the back end for software or anything, so i just need to be able to record stuff and reference it later/report on it. My knowledge of Access ends at sort of 1 degree of complexity (tables/forms/reports/queries and directly referencing each other) so i'm a little lost here. there is no data at all in any of this so I'm perfectly able to destroy/rebuild it however needed. Thanks all