troubleshooting Question

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

Avatar of Davisro
DavisroFlag for United States of America asked on
Microsoft Access
4 Comments2 Solutions13 ViewsLast Modified:
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?

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros