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?