The limit is quite big. 4 is not a large number of tables.  The problem is elsewhere. send a screen shout of the Query design.

Not Updateable

> the query does not work properly.

What doesn't work?

When I run this, I get 42 records which is correct.

When I run this, it cancels all the records and renders only 1 record. If I related the Youth ID in the YouthT to the Youth ID in the Subcontactor T, I get zero records.

I will resolve this by consolidating the Subcontactor T into the Youth T. I will probably have to consolidate other tables too. I think I have too many tables for Dataverse to properly connect to the Access front end.

your Database is all over the place.

for example: TerminationReasonID is stored in YouthT and in DischargeT. That does not make sense.
YouthID is stored in SubContactorT. Why does that table hold anything more than the SubContactor (I assume name) misspelled SunContractor.

You did not select ant fields from SubContractorT

My strong recommendation is to get the relationship seminar and start there

https://599cd.com/1955

Check the JOIN TYPE

Sami, I have to create multiple queries based on different instances for Youth clients. Are you recommending that I don't have multiple foreign ID's in my tables?

If you have 43 rows without SubcontractorT, but only 1 row after joining with SubcontractorT, that means SubcontractorT and YouthT only share ONE common value of SubcontractorID.  For instance, if SubcontractorT contains 1, 2, 3, and 4 for SubcontractorID, and YouthT contains 4, 5, 6, and 7 for SubcontractorID, then yes, only one record will show up, because only "4" is shared by both tables.  When you inner-join two tables, only the records where join fields are equal in both tables will appear.  That is why there is an equal sign in specifying the inner join:

   INNER JOIN ON (Table1.field1 = Table2.field2)

When you get zero record in a join, that means the two sides doesn't have any matching value in the join field.

David
That is NOT what I am saying.
In classical DB design, a Foreign key in table1 will link to a PrimaryKey in tableA.  
you can have Foreignkey1, Foreignkey2, Foreignkey3 in table1 linking to the primary kyes in TableA, TableB, tableC.

I just do not understand your table design

By the way, what is the primary key of YouthT?

Sami, I think I understand. I was thinking about creating a junction T to create a many to many relationship. I since realized I don't need that anymore. I need to clean up some of the tables and delete extraneous FK's.

The PK for YouthT is YouthID.

The screen shot of the YouthT looks busy because it is a linked Dataverse table. The other tables are still offline but will be exported and linked to Dataverse when I finish the DB.  Working in Dataverese adds more complexity and is challenging for me.

Good luck my friend.