troubleshooting Question

Consistent Monthly Headings on Crosstab Query

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft Access
4 Comments1 Solution168 ViewsLast Modified:
A user has requested a report that breaks down counts by month.  They will be able to request the month span within a given year.  Depending what they request there may or may not be totals for all months.

I have created a crosstab query for the data which has the counts for each month in the data pool.  If they want the report for the months 01 - 06 of the year, my crosstab query will have those results for each month.

The issue is creating the report to show these totals.  If they request a report for all months for a given year I need columns for every month 01 - 12.  If I design the report based on the crosstab query with all months I get errors in the report when the user only requests data for month 01 - 03 because columns 04 - 12 don't exist.

I ran into this situation several years ago and requested help from EE.  I'm pretty sure I was given a way to make sure the crosstab query creates column entries for all months, even if there is no data for a given month.  I'm also pretty sure it had to do with setting up all possible column headings as properties in the query.  Sadly that's all I remember and I don't have access to the application I created back then.

Does anyone know how to make sure a crosstab query generates a column for all possible entries, even if there is no data for the column?  Once I have that I can build the report based on the crosstab query and I will not get errors for columns that have no data.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer 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 1 Answer 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