We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Consistent Monthly Headings on Crosstab Query
Posted on 2016-08-29
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
4 Comments
Active today
Insert the months in the property sheet of the query under shown/displayed column headings:
01,02,03,04,05,06,07,08,09
/gustav
01,02,03,04,05,06,07,08,09
/gustav
Hi.
You can use a combination of SQL queries, including a UNION query to get a full year results.
For example, if your query is:
You can assure you have a value for all months modifiing the query in the following way:
You can create the query with VBA Code, and assign this SQL to the original query of the report.
Also, if you post your query, we could help you better.
I hope you find it useful.
Regards.
Antonio (Barcelona, Spain)
You can use a combination of SQL queries, including a UNION query to get a full year results.
For example, if your query is:
TRANSFORM Sum(curAmount) AS SumOfcurAmount
SELECT curYear
FROM (SELECT Year([dtDate]) AS curYear, Month([dtDate]) AS curMonth, tblAccounting.dtDate, tblAccounting.curAmount
FROM tblAccounting)
WHERE curYear=2001
GROUP BY curYear
PIVOT curMonth;
You can assure you have a value for all months modifiing the query in the following way:
TRANSFORM Sum(curAmount) AS SumaDecurAmount
SELECT curYear
FROM (SELECT Year([dtDate]) AS curYear, Month([dtDate]) AS curMonth, curAmount
FROM tblAccounting
UNION
SELECT 2001 as curYear, 1 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 2 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 3 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 4 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 5 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 6 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 7 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 8 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 9 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 10 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 11 as curMonth, 0 as curAmount FROM tblAccounting
UNION
SELECT 2001 as curYear, 12 as curMonth, 0 as curAmount FROM tblAccounting)
WHERE curYear=2001
GROUP BY curYear
PIVOT curMonth;
You can create the query with VBA Code, and assign this SQL to the original query of the report.
Also, if you post your query, we could help you better.
I hope you find it useful.
Regards.
Antonio (Barcelona, Spain)
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month12 days, 4 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.