OK, understand that I don't know the detailed intent of your DB, though I can guess. Therefore, I have no idea whether this is CORRECT. However, this structure is simplified a lot and is FAR less likely to lead to Access having internal confusion when you create queries.
If you were not aware of these ideas, may I respectfully suggest that you do a little reading on Database Normalization? In particular, for the kind of problem we saw in your first diagram, the key concepts would be for parent/child tables (also referred to as independent/dependent tables.)
If you search this forum for Normalization, you could get some articles. If you search the general web, you have to make that Database Normalization because other disciplines use the word "Normalization" as well. If you qualify it by specifying Database Normalization then you will get LOTS of good reading materials. Skim through a few articles to see if any of them "connect" with you. It is a common enough topic that you should get literally thousands of potential hits. I would recommend starting with .EDU domain sites before branching off to other domains. Mostly because the .COM database sites often have something to sell you. (But not always!)