Consistent Monthly Headings on Crosstab Query
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.
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)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial