Crosstab Query has blank values in some columns even though data exists

Experts,  I have a production Access report that is missing data in some columns. The data source is a query that depends on a query that depend on a query etc. so the query chain is about eight queries deep.

One of the dependent queries in the chain is a Crosstab query that has five columns: Cols 2 and 5 display values but Cols 1,3 and 4 are blank. Yet, if I change this exact query in the GUI to a Select query, then all of the data is displayed. When I switch it back to Crosstab, the columns are blank again.

So for example, say i have the following data in a Select query
Rate      Year    Amount
5.OH1      2020      500
5.OH1      2021      500
5.OH1      2022      1200
5.OH1      2023      1500
5.OH1      2024      1700

In Crosstab form it displays as below and therefore does not pass the values in the blank columns to the next query in the chain
Rate      2020   2021   2022   2023   2024
5.OH1                  500                             1700

Any idea why this is happenning and how I can fix it?

Thanks