Did you try bringing in CaliberT a 2nd time then have MagazineT relate to CaliberT and FirearmT relate to CaliberT_1.

I have not but I will. Thank you.

I think that will solve my problem. Thank you.

Multiple Join
This video may help/

Watched it several times. YouTube knows me well. Just having issues grasping it but I think I may have it now. Thank you very much. All help is appreciated.

What happens if you remove the join between CaliberT and FirearmT?

Bryan it works fine.

Donald nailed it above.

You don't need two Caliber tables in your database, but you DO need to bring CaliberT into the query twice if you want to join it to both FirearmT and MagazineT.

Right now you're trying to use one instance of CaliberT for two different relationships at the same time, which forces both joins to match the same record. That's why things break or return no results.

Add CaliberT a second time in the query (Access will name it something like CaliberT_1). Then join one to FirearmT and the other to MagazineT. That lets each side resolve independently.

Also, just something to think about design-wise: if MagazineT is already tied to FirearmT via FirearmID, you may not even need CaliberID in MagazineT unless you specifically want to allow mismatches. Otherwise you're storing the same data in two places and will have to keep it in sync.

Richard Thank you....First question is, those relationship were made by Access automatically, and I understand now about bringing it in twice in the query. So will all be good leaving it like that and just add the second table when I do my queries? When you say mismatches you mean like it is showing now? Because what I want to do is connect the magazines to the firearm they go to when I need to, but I also want to use them by themselves without connecting to the firearm. Hope I explained that correctly.

Yep, you're fine leaving the tables and relationships as they are. The important part is that in the query, if you need to connect both FirearmT and MagazineT to CaliberT, then just bring CaliberT into the query twice. That's normal and that's how Access handles it. By "mismatches" I mean situations where a magazine could be assigned one caliber and the firearm it's tied to could be assigned a different caliber. If you want magazines to be usable by themselves sometimes, then keeping CaliberID in MagazineT makes sense. If you also want to optionally connect a magazine to a firearm, that's fine too. Just understand that once you store caliber in both places, it's up to you to make sure they stay consistent when a magazine is linked to a firearm. So yes, your design can work, just be aware you're trading a little redundancy for flexibility.

Gotcha. Thank you. I understand so basically anytime I have the joins like that just bring table in a second time and split it off. Understand.