In Access 2003, I have a crosstab query where the Column Headings are months found in a date field (formatted as "yyyy-mm"). The Row Headings are the Accounts. The Value column is the sum of currency values in the Paid field. The records are filtered to be those within a date range of another date field. The query returns columns for each month that has at least 1 sum. But my boss wants to see columns for every month in the date range, even if they are empty. How do I do that?
Force empty columns in a crosstab query
- Thread starter bobfin
- Start date
Kiwiman
Registered User
- Local time
- Today, 16:56
- Joined
- Apr 27, 2008
- Messages
- 799
Howzit
This will get you started, but is not dynamic - i.e will only work for the 2008 year. There must be a way to do it, I just don't have that knowledge.
The cross tab will only return a column for each option you put there, even if there are no results. It will not return any columns that have results, but the headings do not meet what you put in the Column Headings section - i.e. if you have results being returned for 2009-01, it will not show in the results.
A bit messy - but I got round the dynamic side of things by creating a what I call "a botch crosstab" query with the 12 separate fields for each month using the iif statement:
Then creating a new query to sum all the results - using this query as the basis of my reports
Probably best shown in a sample database...
This will get you started, but is not dynamic - i.e will only work for the 2008 year. There must be a way to do it, I just don't have that knowledge.
- In design of the report rightclick on column headings and select properties
- In the column headings fields type "2008-01","2008-02","2008-03"..."2008-12"
The cross tab will only return a column for each option you put there, even if there are no results. It will not return any columns that have results, but the headings do not meet what you put in the Column Headings section - i.e. if you have results being returned for 2009-01, it will not show in the results.
A bit messy - but I got round the dynamic side of things by creating a what I call "a botch crosstab" query with the 12 separate fields for each month using the iif statement:
Code:
Mth1:iif(month(mydate)=1,myfield,0),mth2:iif(month(mydate)=2,myfield,0)...Then creating a new query to sum all the results - using this query as the basis of my reports
Probably best shown in a sample database...
Thank you for that sample database... it helped my problem a lot. I was trying to find a way to make a static frame for my crosstab query
